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

  • 回答 (2)
  • 关注 (0)
  • 查看 (18)

首先,感谢任何帮助我解决这个问题的人。我正在使用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

我该怎么做呢?我很困惑。

乐逍遥乐逍遥提问于
leosslyc回答于
已采纳

将需要一个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

根据潜在日期范围的大小,填充表变量可能需要一段时间。例如,如果范围跨越一到二十年。

Amor陌a回答于

听起来你可能想。特别是作为一个更大的业务组织的一部分,这将变得非常有用。

生成日历之后,可以使用以下内容获得表:

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

这将为日历文件中的至少一行mySPTable的开始日期和结束日期之间的每个日期生成一行。

另外,还可以使用递归的CTE生成日期范围,但特别是从长远来看,我建议生成和使用日历文件。

快速CTE:

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

扫码关注云+社区