Interesting SQL Join on dates between dates

问题:

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.

回答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.

回答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])

回答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)