Is it possible to effect a join against a table of consecutive dates without having to maintain an actual table of consecutive dates?

Question

I have a table with dated records. I want a query that will show the number of records for each date.

Not hard, just a GROUP BY, right? Sure.

But I want to also show days where there were NO records. In this case, I would need to left join against a one-column table of consecutive dates, since a GROUP BY isn't going to show a date that doesn't exist.

Is there a better way of doing this? Or do I have to create a populate a table of dates just so I can join against it?

Answer 1

That would be the simplest solution. Once you have that table, I imagine you will find lots of uses for it. The key is to have an annual job that will add new dates to it or it will get out of synch with the current dates in the system. And don't forget to index it since you will be joining on it.

Or you could populate this in a temp table or table variable at run time, but that might be more time consuming at run time depending on how big a range of dates you need for each run. If you are going to just be looking for last month's dates, this may be the way to go, if you need the last 5 years' worth, I'd go with a pre-populated dates table.

Answer 2

It would probably be easiest to build out the dates table

For example, if you wanted all dates from Jan 1, 2009 to today you could do the following:

DECLARE @start  AS DATETIME,
        @end    AS DATETIME

SELECT  @start = '2009-01-01',
        @end   = getdate()

DECLARE @dates TABLE    (
                        dt  DATETIME
                        )

WHILE (@start < @end)
BEGIN
    INSERT  @dates
    SELECT  @start

    SELECT @start = DATEADD(day, 1, @start)
END

SELECT  *
FROM    @dates

@dates will have a record for each day from @start to @end.

Answer 3

A fun variant on Jon's solution using cross joins:

DECLARE @BaseDate DATETIME
Select @BaseDate = getdate() - 365

DECLARE @digits TABLE (d char(1))

DECLARE @i int
SET @i = 0
WHILE (@i < 10) BEGIN
   INSERT @digits SELECT cast(@i as char(1))
   Select @i = @i +1
END

SELECT DATEADD(d, cast(d1.d + d2.d + d3.d as int), @BaseDate) 
FROM @digits d1 CROSS JOIN @digits d2 CROSS JOIN @digits d3
WHERE cast(d1.d + d2.d + d3.d as int) < 365
ORDER BY d1.d, d2.d, d3.d

Answer 4

If you really don't want a table.

On Oracle you could do something like:

select D.D, count(T.CompareDate)
from (select to_date('2009-07-01', 'YYYY-MM-DD') + LEVEL - 1 as D
  from dual
  connect by LEVEL <= 30) D
left outer join T ON T.CompareDate = D.D
group by D.D

Where the date inside to_date is the starting date, and you test against level for the number of days you want in your results.

On SQL Server 2005/2008:

; with X1 (X) as 
    (select 1 union all select 1)
  , X2 (X) as
    (select 1 from X1 a cross join X1 b)
  , X4 (X) as
    (select 1 from X2 a cross join X2 b)
  , X8 (X) as
    (select 1 from X4 a cross join X4 b)
  , X16 (X) as 
    (select 1 from X8 a cross join X8 b)
  , NUM (N) as
    (select row_number() over (order by X) from X16)
  , D (D) as
    (select dateadd(day, N-1, '20090701')
    from NUM
    where NUM.N <= 30)
select D.D, count(T.CompareDate)
from D
left outer join T on T.CompareDate = D.D
group by D.D

The with clause is to build the Dates. The date specified in the dateadd is starting date, and number of days is tested at NUM.N <=30. You could also test against end date where dateadd(day, N-1, StartDate) <= EndDate

I would recommend encapsulating the with clause to create the ranges as an inline table valued function.

The number generation is based on code I've seen from Itzik Ben-gan. Each X gives you number of rows equal to power of 2 of the X number (X1 = 2 rows and X8 = 256 rows.) If you need more that 65,536, you will need to add more cross joins. If you never need more than 256, then you can elimanate X16.

Also, if you have a table of numbers lying around, you can use that and date arithemetic to generate the dates you need on the fly.

Answer 5

With SQL Server 2005 it's a lot easier - you can use this date/time interval function:

fn_DateRange()

works like:

-- Hourly blocks
select
   dr.startdate
from
   fn_DateRange('12/14/2008 08:00:00', '12/14/2008 12:00:00', '01:00:00') dr
go
startdate
-----------------------
2008-12-14 08:00:00.000
2008-12-14 09:00:00.000
2008-12-14 10:00:00.000
2008-12-14 11:00:00.000
2008-12-14 12:00:00.000

-- Daily blocks
select
   dr.startdate
from
   fn_DateRange('12/14/2008', '12/18/2008', 1) dr
go
startdate
-----------------------
2008-12-14 00:00:00.000
2008-12-15 00:00:00.000
2008-12-16 00:00:00.000
2008-12-17 00:00:00.000
2008-12-18 00:00:00.000

-- Weekly blocks
select
   dr.startdate
from
   fn_DateRange('12/14/2008', '01/14/2009', 7) dr
go
startdate
-----------------------
2008-12-14 00:00:00.000
2008-12-21 00:00:00.000
2008-12-28 00:00:00.000
2009-01-04 00:00:00.000
2009-01-11 00:00:00.000

very handy to join in your query ...

Ron