Interesting SQL Join on dates between dates

Question

First off, thanks to anyone who helps me solve this problem. I am using SQL 2005, but can use 2008 if no solution is available in 05.

I have a rows of data that look like such:

select * from mySPtable

| myPK | Area | RequestType |  StartDate  |  EndDate  |
   1      SB        ADD        8/14/2011    8/18/2011
   2      NB        RMV        8/16/2011    8/16/2011

So what I want to do is count up the total requests for each area by day. Results should be:

|  myDate  | RequestType |  Area  | myCount |
  8/14/2011      ADD         SB        1
  8/15/2011      ADD         SB        1
  8/16/2011      ADD         SB        1
  8/16/2011      RMV         NB        1
  8/17/2011      ADD         SB        1
  8/18/2011      ADD         SB        1

How do I go about doing this? I'm stumped and no amount of googling has helped.

Solution 1

You'll need either a Calendar table or you can generate one with a CTE. Once you have that, the rest of the query should be fairly trivial. The CTE approach can be a little complex due to recursion issues and not being allowed to use aggregates, so below I've used a table variable. You can also make this a permanent table that you keep in your database.

SET NOCOUNT ON

DECLARE @Calendar TABLE (my_date DATETIME NOT NULL)
DECLARE @date DATETIME, @max_date DATETIME

SELECT @date = MIN(StartDate), @max_date = MAX(EndDate) FROM My_Table

WHILE (@date <= @max_date)
BEGIN
    INSERT INTO @Calendar (my_date) VALUES (@date)
    SELECT @date = DATEADD(dy, 1, @date)
END

SELECT
    C.myDate,
    M.RequestType,
    M.Area,
    COUNT(*) AS myCount
FROM
    @Calendar C
INNER JOIN My_Table M ON
    M.StartDate <= C.myDate AND
    M.EndDate >= C.myDate
GROUP BY
    C.myDate,
    M.RequestType,
    M.Area
ORDER BY
    C.myDate,
    M.RequestType,
    M.Area

Depending on how large your potential date range is, filling the table variable could take awhile. For example, if the range spanned a decade or two.

Solution 2

Sounds like you may want a 'Calendar' file. Especially as part of a larger business organization, this will become extremely useful.

After generating the calendar, you can get your table with the following:

SELECT a.isoDate, b.RequestType, b.Area, count(*)
FROM calendar as a
JOIN mySPTable as b
ON a.isoDate between b.StartDate and b.EndDate
WHERE a.isoDate >= [input_start_date] 
      AND a.isoDate < [input_end_date]
GROUP BY a.isoDate, b.RequestType, b.Area

This will generate a row for every date in the calendar file that is between the start and end dates for at least one row of mySPTable.

As a side note, it's also possible to generate the range of dates with a recursive CTE, but especially over the long run, I'd recommend generating and using the calendar file.
Quick CTE:

WITH DateRange (thisDate) as (SELECT [input_start_date]
                              UNION ALL
                              SELECT DATEADD(dy, 1, thisDate)
                              FROM DateRange
                              WHERE thisDate < [input_end_date])

Solution 3

You can do this with a number table (starting from 0). Here I use master..spt_values instead. SQL, Auxiliary table of numbers

select dateadd(day, N.Number, M.StartDate) as myDate,
       RequestType,
       Area, 
       count(*) as myCount
from mySPtable as M
  inner join master..spt_values as N
    on N.Number <= datediff(day, M.StartDate, M.EndDate)
where N.type = 'P'
group by dateadd(day, N.Number, M.StartDate),
         RequestType,
         Area
order by dateadd(day, N.Number, M.StartDate)

Comments

[Jim Garrison]

Does the first row (myPK == 1) imply a total of five requests, one per day?

[Cygnusx1]

would adding a table with all the dates not be easier and faster? instead of creating it at runtime? btw nice solution...

[Tom H]

That's how I would normally do it, and I did mention that you could make it a permanent table. I usually have a Calendar in my databases where I can also include things like is_holiday, is_weekday, financial_quarter, etc. to make a lot of queries easier.

[HABO]

I'm a fan of CTEs, but be aware there is a limit on the number of recursions. The absolute limit is 32,767 and the default limit is 100. See MAXRECURSION at msdn.microsoft.com/en-us/library/ms175972.aspx.

[Clockwork-Muse]

I keep forgetting that SQL Server has that default - DB2 doesn't, and I think the limit is getting an out-of-memory error. Of course, if you're running over more than about a year's worth of dates, you probably want an actual calendar file anyways...

[HABO]

Correction: Setting MAXRECURSION to zero disables any limit. My bad.

[Steve McCall]

I found this very helpful for something related that I was stuck on!