Split/extrapolate single records by date range into multiple records

Question

I have data like below with a start and end date. The record could start and finish on the same day, or could span many days.

I want to expand each record that I have one entry for each day. For example:

AAAA 09/10/2015 15:30 09/11/2015 16:00

would become

AAAA 09/10/2015 15:30 09/11/2015 00:00    
AAAA 09/11/2015 00:00 09/11/2015 16:00

Can this be done within a query using a for each loop?

Is your sample input and output from a _single_ column, or is it actually multiple columns which you have made to appear concatenated? — Tim Biegeleisen, Sep 22 '15 at 04:14
You need a calendar table and join on it... Search the stack for that. — Giorgi Nakeuri, Sep 22 '15 at 04:30
@TimBiegeleisen It is actually multiple columns. Sorry for the confusion. — Bob, Sep 23 '15 at 15:40
You should really the thoughtful answers given below, and mark one correct if it solved your problem. — Tim Biegeleisen, Sep 24 '15 at 06:00

score 2 · Answer 1 · answered Sep 22 '15 at 04:33

This is one of many situations where it's nice to have a calendar lookup table. There are numerous examples of calendar table scripts to build something as simple or as robust as you could want, but let's just pretend all you have is a list of dates in your calendar table.

Normally you'd just join using BETWEEN, but since you want the start and end time portions to be preserved, you'll need a little additional logic, using CASE expressions, CAST() and DATEADD() you can get what you're after, something like this:

SELECT col1
      ,CASE WHEN CAST(start_dt AS DATE) = b.cal_dt THEN start_dt
            WHEN CAST(end_dt AS DATE) = b.cal_dt THEN CAST(CAST(end_dt AS DATE)AS DATETIME)
            ELSE CAST(cal_dt AS DATETIME)
       END AS start_dt
       ,CASE WHEN CAST(start_dt AS DATE) = b.cal_dt THEN CAST(DATEADD(day,1,CAST(start_dt AS DATE))AS DATETIME)
            WHEN CAST(end_dt AS DATE) = b.cal_dt THEN end_dt
            ELSE CAST(DATEADD(day,1,cal_dt) AS DATETIME)
        END AS end_dt
FROM Table1 a
JOIN lkp_Calendar b
  ON b.cal_dt BETWEEN CAST(start_dt AS DATE) AND CAST(end_Dt AS DATE)

score 1 · Answer 2 · edited May 23 '17 at 12:26

You can generate your calendar table on the fly using Itzik Ben-Gan's cascaded/stack CTEs:

DECLARE @Range AS INT

SELECT TOP 1 @Range = 
    DATEDIFF(DAY, MIN(start_date), MAX(end_date)) + 1
FROM yourTable
GROUP BY col1 
ORDER BY DATEDIFF(DAY, MIN(start_date), MAX(end_date)) + 1 DESC


;WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
Tally(N) AS(
    SELECT TOP(@Range) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
),
CteMinMax(col1, sdt, edt) AS(
    SELECT col1, MIN(start_date), MAX(end_date)
    FROM yourTable  
    GROUP BY col1
),
CteDates(col1, sdt, edt) AS(
    SELECT
        m.col1,
        DATEADD(DAY, t.N-1, CAST(m.sdt AS DATE)),
        DATEADD(DAY, t.N, CAST(m.sdt AS DATE))
    FROM CteMinMax m
    CROSS JOIN Tally t
    WHERE DATEADD(DAY, t.N-1, CAST(m.sdt AS DATE)) < DATEADD(DAY, 1, CAST(m.edt AS DATE))
)
SELECT
    t.col1,
    start_date =
        CASE
            WHEN t.start_date > d.sdt THEN t.start_date
            ELSE CAST(d.sdt AS DATETIME)
        END,
    end_date = 
        CASE
            WHEN t.end_date >= d.edt THEN CAST(d.edt AS DATETIME)
            ELSE t.end_date
        END
FROM yourTable t
INNER JOIN CteDates d
    ON d.sdt >= CAST(t.start_date AS DATE) 
    AND d.sdt < DATEADD(DAY, 1, CAST(t.end_date AS DATE))

SQL Fiddle

score 0 · Answer 3 · edited Sep 23 '15 at 21:18

Thanks Hart. Your logic worked with a little change.

SELECT col1
      ,CASE WHEN CAST(start_dt AS DATE) = b.cal_dt THEN start_dt
            WHEN CAST(end_dt AS DATE) = b.cal_dt THEN 
                CAST(CAST(end_dt AS DATE)AS DATETIME)
                ELSE CAST(cal_dt AS DATETIME)
           END AS start_dt
           ,CASE  WHEN CAST(start_dt AS DATE) = CAST(end_dt AS DATE) THEN end_dt
            WHEN CAST(start_dt AS DATE) = b.cal_dt THEN
                    CAST(DATEADD(day,1,CAST(start_dt AS DATE))AS DATETIME)
                    WHEN CAST(end_dt AS DATE) = b.cal_dt THEN end_dt
                    ELSE CAST(DATEADD(day,1,cal_dt) AS DATETIME)
                END AS end_dt
        FROM Table1 a
        JOIN lkp_Calendar b
          ON b.cal_dt BETWEEN CAST(start_dt AS DATE) AND CAST(end_Dt AS DATE)

Split/extrapolate single records by date range into multiple records

3 Answers3

Linked

Related