有趣的SQL连接日期之间的日期

Question

首先，感谢任何帮助我解决此问题的人。我正在使用SQL 2005，但如果05中没有可用的解决方案，则可以使用2008。

我有一排数据看起来像这样：

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

因此，我要做的是每天计算每个区域的总请求量。结果应为：

|  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

我该怎么做呢？我很沮丧，没有任何谷歌搜索帮助。

Answer 1

您将需要一个Calendar表，或者可以使用CTE生成一个。一旦有了这些，其余的查询就应该变得无关紧要了。由于递归问题并且不允许使用聚合，因此CTE方法可能有点复杂，因此下面我使用了一个表变量。您还可以使该表成为永久表，并将其保留在数据库中。

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

根据您可能的日期范围有多大，填充表变量可能需要一段时间。例如，如果范围跨越了十年或两年