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.

Answer 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.