Sql-server – Split date range into separate records

sql serversql server 2014

Today I have a problem with splitting a date range so it becomes two separate records.

Here is an example

----------------------------------------------------------------
| Record id | date_from               | date_to                |
----------------------------------------------------------------
|     A     | 2017-02-03 08:00:00.000 | 2017-02-04 17:00:00.000|
----------------------------------------------------------------

For the result I want this

----------------------------------------------------------------
| Record id | date_from               | date_to                |
----------------------------------------------------------------
|     A     | 2017-02-03 08:00:00.000 | 2017-02-03 23:59:59.000|
----------------------------------------------------------------
|     A     | 2017-02-04 00:00:00.000 | 2017-02-04 17:00:00.000|
----------------------------------------------------------------

Is there anything that can enlighten me with this problem? Thank you very much for your advice.

PS: this is dynamic and there is no limit in duration. If the "from" day is 2017-02-02 at 17:00, and the "to" day is 2017-02-04 17:00 then there will be three records, one of which is date range from 2017-02-03 00:00:00 until 2017-02-03 23:59:59.

For midnight, I guess it is based on the date time default. For the real problem, I have this table:

enter image description here

As you can see, from the detail for seeding mailbox databse, the range is like one day as assumption, so I guess it is more like that example 🙂

PS: I am using SQL Server 2014.

Best Answer

One way to do it is to use a table of numbers and CROSS APPLY.

Sample data

DECLARE @T TABLE (RecordID int, date_from datetime2(0), date_to datetime2(0));

INSERT INTO @T (RecordID, date_from, date_to) VALUES
(1, '2017-02-03 08:00:00' , '2017-02-04 17:00:00'),
(2, '2017-02-05 08:00:00' , '2017-02-05 17:00:00'),
(3, '2017-02-06 08:00:00' , '2017-02-10 17:00:00');

Query

In this example I generated a table of 10 numbers on the fly (CTE_Numbers). In production I have a permanent table with 100K numbers.

WITH 
CTE_Numbers1(n)
AS
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)
,CTE_Numbers
AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY n) AS Number
    FROM CTE_Numbers1
)
SELECT
    T.RecordID
    ,CASE WHEN CA.Number0 = 0
    THEN date_from
    ELSE DATEADD(day, CA.Number0, CAST(T.date_from as date))
    END AS new_date_from
    ,CASE WHEN CA.Number0 = DATEDIFF(day, T.date_from, T.date_to)
    THEN date_to
    ELSE DATEADD(day, CA.Number0 + 1, CAST(T.date_from as date))
    END AS new_date_to
FROM
    @T AS T
    CROSS APPLY
    (
        SELECT CTE_Numbers.Number - 1 AS Number0
        FROM CTE_Numbers
        WHERE CTE_Numbers.Number <= DATEDIFF(day, T.date_from, T.date_to) + 1
    ) AS CA
ORDER BY
    RecordID
    ,new_date_from
;

Result

+----------+---------------------+---------------------+
| RecordID |    new_date_from    |     new_date_to     |
+----------+---------------------+---------------------+
|        1 | 2017-02-03 08:00:00 | 2017-02-04 00:00:00 |
|        1 | 2017-02-04 00:00:00 | 2017-02-04 17:00:00 |
|        2 | 2017-02-05 08:00:00 | 2017-02-05 17:00:00 |
|        3 | 2017-02-06 08:00:00 | 2017-02-07 00:00:00 |
|        3 | 2017-02-07 00:00:00 | 2017-02-08 00:00:00 |
|        3 | 2017-02-08 00:00:00 | 2017-02-09 00:00:00 |
|        3 | 2017-02-09 00:00:00 | 2017-02-10 00:00:00 |
|        3 | 2017-02-10 00:00:00 | 2017-02-10 17:00:00 |
+----------+---------------------+---------------------+
Related Question