CODEWITHSUNDEEP
sqlsql-server
Yuu
Yuu

Reputation: 619

Create a recursive list of dates given from two Different Dates and items

I'm having a trouble of creating a list of dates given with a two or more different items. Here's what my data looks like

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

Is this possible? Thanks

Upvotes: 4

Views: 938

Answers (1)

D-Shih
D-Shih

Reputation: 46249

You can try to use CTE recursion to make it.

The keypoint is, let startTime column add one day then convert DateTime to Date in the CTE recursion condition.

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');

Query 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 |

Upvotes: 3

Related Questions