April 4, 2014 at 9:00 am
Hi,
Perhaps a misinterpretation but I am stuck in a very simple query that's puzzling me ...
Situation:
I have 2 tables (I'll stick to the relevant fields only)
1. PERDAT with fields (1) "year" (int), (2) "period" (varchar(3)), (3) "startdate" (datetime) and (4) "enddate" (datetime)
2. PROJECTS with fields (1) "projectnumber" (varchar(20), (2) "startdate-contract" (datetime) and (3) "enddate_contract" (datetime)
For every project I would like to have all periods from PERDAT that belong to the year of the startdate of the project. Only the record in the perdat-table that has the startdate of the project between the start and enddate in the perdat-table should show more info.
My query is:
SELECTpd.year,
pd.period,
pd.startdate,
df.projectnumber,
df.startdate_contract,
df.enddate_contract,
MONTH(df.startdate_contract) AS month
FROMperdat pd (NOLOCK)
LEFT OUTER JOIN ProjectDateFields df (NOLOCK) ON df.startdate_contract BETWEEN pd.startdate AND pd.enddate
WHEREdf.projectnumber = 'CR1003753'
The resultset I get:
yearperiodstartdateprojectnumberstartdate_contractenddate_contractmonth
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
2014 32014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
What I would like to have is:
yearperiodstartdateprojectnumberstartdate_contractenddate_contractmonth
201412014-01-01 00:00:00.000NULLNULLNULLNULL
201422014-02-01 00:00:00.000NULLNULLNULLNULL
201432014-03-01 00:00:00.000CR10037532014-03-01 00:00:00.000NULL3
201442014-04-01 00:00:00.000NULLNULLNULLNULL
201452014-05-01 00:00:00.000NULLNULLNULLNULL
201462014-06-01 00:00:00.000NULLNULLNULLNULL
201472014-07-01 00:00:00.000NULLNULLNULLNULL
201482014-08-01 00:00:00.000NULLNULLNULLNULL
201492014-09-01 00:00:00.000NULLNULLNULLNULL
2014102014-10-01 00:00:00.000NULLNULLNULLNULL
2014112014-11-01 00:00:00.000NULLNULLNULLNULL
2014122014-12-01 00:00:00.000NULLNULLNULLNULL
I thought it was a simple left join ...
What do I overlook?
Thanks!
regards
Michiel
April 4, 2014 at 9:07 am
Please read the article in my signature about posting questions. We will need some sample tables and sample data to test our solutions. Thanks
After reading the problem the issue is with your where statement. You have now made this an inner join. remove the where statement and replace it with an AND as part of your join criteria.
April 4, 2014 at 9:08 am
As soon as you added df to where clause it became an inner join.
April 4, 2014 at 9:10 am
Try this...
SELECTpd.year,
pd.period,
pd.startdate,
df.projectnumber,
df.startdate_contract,
df.enddate_contract,
MONTH(df.startdate_contract) AS month
FROMperdat pd (NOLOCK)
LEFT OUTER JOIN ProjectDateFields df (NOLOCK) ON df.startdate_contract BETWEEN pd.startdate AND pd.enddate
and df.projectnumber = 'CR1003753'
April 4, 2014 at 9:26 am
Sarah Wagner (4/4/2014)
Try this...SELECTpd.year,
pd.period,
pd.startdate,
df.projectnumber,
df.startdate_contract,
df.enddate_contract,
MONTH(df.startdate_contract) AS month
FROMperdat pd (NOLOCK)
LEFT OUTER JOIN ProjectDateFields df (NOLOCK) ON df.startdate_contract BETWEEN pd.startdate AND pd.enddate
and df.projectnumber = 'CR1003753'
Or try it without the NOLOCK hints which will only make your queries return inconsistent information (you can have either missing rows or duplicate rows).
Check the following articles on this hint.
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply