views:

1890

answers:

8

I just want a quick way (and preferably not using a while loop)of createing a table of every date between date @x and date @y so I can left outer join to some stats tables, some of which will have no records for certain days in between, allowing me to mark missing days with a 0

A: 

Just: WHERE col > start-date AND col < end-date

Oli
He's trying to create a table to do a left outer join on. This will not do what he wants to do.
Charles Graham
+3  A: 

I believe you're looking for this blog post.

bdukes
It's dirty but it does the job
digiguru
And it's portable to other SQL engines (including Server 2000). :)
IDisposable
A: 

I think that you might as well just do it in a while loop. I know it's ugly, but it's easy and it works.

Charles Graham
A: 

I was actually doing something similar a little while back, but I couldn't come up with a way that didn't use a loop.

The best I got was a temp table, and then selecting the dates I wanted to join on into that.

The blog bduke linked to is cute, although I think the temp table solution is perhaps a cleaner solution.

Dana
A: 

I've found another table that stores every date (it's visitors to the website), so how about this...

Declare @FromDate datetime,  
     @ToDate datetime  
Declare @tmpDates table   
      (StatsDate datetime)
Set @FromDate = DateAdd(day,-30,GetDate())
Set @ToDate = GetDate()

Insert Into  @tmpDates (StatsDate)
Select 
    distinct CAST(FLOOR(CAST(visitDate AS DECIMAL(12, 5))) AS DATETIME)
FROM tbl_visitorstats 
Where visitDate between @FromDate And @ToDate 
Order By CAST(FLOOR(CAST(visitDate AS DECIMAL(12, 5))) AS DATETIME) 


Select * FROM @tmpDates

It does rely on the other table having an entry for every date I want, but it's 98% likely there'll be data for every day.

digiguru
+9  A: 

Strictly speaking this doesn't exactly answer your question, but its pretty neat.

Assuming you can live with specifying the number of days after the start date, then using a Common Table Expression gives you:

WITH numbers ( n ) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM numbers WHERE n < 500 )
    SELECT DATEADD(day,n-1,'2008/11/01') FROM numbers
    OPTION ( MAXRECURSION 500 )
Macka
You are a genius! Well done, didn't think about using CTE!
digiguru
The Option clause does not accept a variable as input, so one must know how many days at the time of writing the code. Otherwise: fun stuff.
David B
But the Where clause does, the MAXRECURSION is just a fail over if the loop goes on to far.
digiguru
If you can't use CTEs (like in SQL Server 2000), then you can use what I wrote here http://musingmarc.blogspot.com/2006/07/need-date-range-in-sql-without-filling.html
IDisposable
completely awesome!
orip
A: 

Just write the loop. Someone has to write a loop for this, be it you - or SQL Server.

DECLARE @Dates TABLE
(
  TheDate datetime PRIMARY KEY
)
DECLARE @StartDate datetime, @EndDate datetime
SELECT @StartDate = '2000-01-01', @EndDate = '2010-01-01'


DECLARE @LoopVar int, @LoopEnd int    
SELECT @LoopEnd = DateDiff(dd, @StartDate, @EndDate), @LoopVar = 0


WHILE @LoopVar <= @LoopEnd
BEGIN
  INSERT INTO @Dates (TheDate)
  SELECT DateAdd(dd,@LoopVar,@StartDate)

  SET @LoopVar = @LoopVar + 1
END


SELECT *
FROM @Dates
David B
People that downvote without commenting... lol.
David B
+1  A: 

I would create a Calendar table that just contained every date from a suitable start date until a suitable end date. This wouldn't take up much space in your database and would make these types of query child's play.

select  ...
from    Calendar
        left outer join
        ...
where   Calendar.Date >= @x
and     Calendar.Date <= @y
Garry Shutler