SQL Server，将持续时间行按24小时周期分割

Question

我正在使用sql server 2008 r2。我正在尝试在 FromDate 和 Todate
之间按24小时周期范围划分行。例如，如果给定的时间行如下（（ FromDate 和 Todate 之间的范围是4天，那么我要4行）

ID   FromDate                 Todate     
---- ------------------------ -------------------------
1    2014-04-01 08:00:00.000  2014-04-04 12:00:00.000

我想要看到的结果是这样的：

ID   FromDate                 Todate                   DateDiff(HH)
---- ------------------------ -----------------------------------
1    2014-04-01 08:00:00.000  2014-04-01 23:59:59.000  15
1    2014-04-02 00:00:00.000  2014-04-02 23:59:59.000  23
1    2014-04-03 00:00:00.000  2014-04-03 23:59:59.000  23
1    2014-04-04 00:00:00.000  2014-04-04 12:00:00.000  12

Answer 1

试试这个查询：

WITH TAB1 (ID,FROMDATE,TODATE1,TODATE) AS
(SELECT ID,
FROMDATE,
DATEADD(SECOND, 24*60*60 - 1, CAST(CAST(FROMDATE AS DATE) AS DATETIME)) TODATE1,
TODATE 
FROM TABLE1
UNION ALL
SELECT 
ID,
DATEADD(HOUR, 24, CAST(CAST(TODATE1 AS DATE) AS DATETIME)) FROMDATE,
DATEADD(SECOND, 2*24*60*60-1, CAST(CAST(TODATE1 AS DATE) AS DATETIME)) TODATE1,
TODATE
FROM TAB1 WHERE CAST(TODATE1 AS DATE) < CAST(TODATE AS DATE)
),
TAB2 AS
(SELECT ID,FROMDATE,
CASE WHEN TODATE1 > TODATE THEN TODATE ELSE TODATE1 END AS TODATE
FROM TAB1)
SELECT TAB2.*,
DATEPART(hh, TODATE) - DATEPART(hh, FROMDATE) [DateDiff(HH)] FROM TAB2;