Why can't get all dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Why can't get all dates

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-03-31 : 13:24:36

I want rows for all dates in range. So I am using a table to insert the dates in range, then
joining it to my table like:


DECLARE @dates table (sdate datetime)

INSERT @dates (sdate)
SELECT dateadd(day, number, @Loc_START_DATE)
FROM   master..spt_values
WHERE  type = 'P'
AND    number < datediff(day, @Loc_START_DATE, @Loc_END_DATE + 1)
...
SELECT...
FROM @dates d 
LEFT OUTER JOIN SAMPLE_DATA SMPD 
ON d.sdate = SAMPLE_START_DATE

since I am using LEFT join, shouldn't it give me all the rows?
But its returning only rows with matching dates in the other table.
Any ideas? Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-31 : 13:33:46

It should give all the rows from @date regardless of match...But I'm assuming you are having a where clause following this which you've not posted and using column from SAMPLE_DATA table in it which is causing it to reduce to an inner join functionality

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-03-31 : 18:41:10

Thanks visakh, I found it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 04:53:45

gr8

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources