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

Question

我正在使用 sql server 2008 r2。我正在尝试按 之间的 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;

SQL Fiddle