sql - 创建由两个不同日期和项目给出的日期的递归列表

Question

我在创建包含两个或多个不同项目的日期列表时遇到了麻烦。这是我的数据的样子

RAW DATA
ID| Start Date Time     | End Date Time
1 | 2018-08-06 13:00:00.000 | 2018-08-07 10:00:00.000
2 | 2018-08-10 08:00:00.000 | 2018-08-10 15:00:00.000

RESULT
ID| Start Date Time     | End Date Time
1 | 2018-08-06 13:00:00.000 | 2018-08-06 23:59:59.000
1 | 2018-08-07 00:00:01.000 | 2018-08-07 10:00:00.000
2 | 2018-08-10 08:00:00.000 | 2018-08-10 15:00:00.000

这可能吗？谢谢

Answer 1

您可以尝试使用CTE递归来实现。

关键点是，让startTime列添加一天，然后在CTE递归中将DateTime转换为Date情况。

CREATE TABLE T(
  ID INT,
  [Start Date Time] DATETIME,
  [End Date Time] DATETIME
);
INSERT INTO T VALUES (1,'2018-08-06 13:00:00.000','2018-08-07 10:00:00.000');
INSERT INTO T VALUES (2,'2018-08-10 08:00:00.000','2018-08-10 15:00:00.000');

查询 1:

;WITH CTE AS (
    SELECT ID,[Start Date Time] startTime,[End Date Time] endTime
    FROM T
    UNION ALL
    SELECT ID,CAST(CAST(DATEADD(DAY,1,startTime)AS DATE) AS DATETIME),endTime 
    FROM CTE
    WHERE CAST(CAST(DATEADD(DAY,1,startTime) AS DATE) AS DATETIME) < endTime
)
SELECT id,
       startTime AS 'Start Date Time',
       (CASE WHEN LEAD(startTime,1,endTime) OVER(PARTITION BY ID ORDER BY startTime)= endTime 
                 THEN endTime
                 ELSE DATEADD(second,-1,LEAD(startTime,1,endTime) OVER(PARTITION BY ID ORDER BY startTime)) 
            END) AS 'End Date Time' 
FROM CTE

<强> Results :

| id |      Start Date Time |        End Date Time |
|----|----------------------|----------------------|
|  1 | 2018-08-06T13:00:00Z | 2018-08-06T23:59:59Z |
|  1 | 2018-08-07T00:00:00Z | 2018-08-07T10:00:00Z |
|  2 | 2018-08-10T08:00:00Z | 2018-08-10T15:00:00Z |