Split/extrapolate Single Records By Date Range Into Multiple Records

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

Solution 1:

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
      ,CASEWHENCAST(start_dt ASDATE) = b.cal_dt THEN start_dt
            WHENCAST(end_dt ASDATE) = b.cal_dt THENCAST(CAST(end_dt ASDATE)AS DATETIME)
            ELSECAST(cal_dt AS DATETIME)
       ENDAS start_dt
       ,CASEWHENCAST(start_dt ASDATE) = b.cal_dt THENCAST(DATEADD(day,1,CAST(start_dt ASDATE))AS DATETIME)
            WHENCAST(end_dt ASDATE) = b.cal_dt THEN end_dt
            ELSECAST(DATEADD(day,1,cal_dt) AS DATETIME)
        ENDAS end_dt
FROM Table1 a
JOIN lkp_Calendar b
  ON b.cal_dt BETWEENCAST(start_dt ASDATE) ANDCAST(end_Dt ASDATE)

Solution 2:

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

DECLARE@RangeASINTSELECT TOP 1@Range= 
    DATEDIFF(DAY, MIN(start_date), MAX(end_date)) +1FROM yourTable
GROUPBY col1 
ORDERBY DATEDIFF(DAY, MIN(start_date), MAX(end_date)) +1DESC


;WITH E1(N) AS( -- 10 ^ 1 = 10 rowsSELECT1FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT1FROM E1 a CROSSJOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT1FROM E2 a CROSSJOIN E2 b), -- 10 ^ 4 = 10,000 rows
Tally(N) AS(
    SELECT TOP(@Range) ROW_NUMBER() OVER(ORDERBY(SELECTNULL))
    FROM E4
),
CteMinMax(col1, sdt, edt) AS(
    SELECT col1, MIN(start_date), MAX(end_date)
    FROM yourTable  
    GROUPBY col1
),
CteDates(col1, sdt, edt) AS(
    SELECT
        m.col1,
        DATEADD(DAY, t.N-1, CAST(m.sdt ASDATE)),
        DATEADD(DAY, t.N, CAST(m.sdt ASDATE))
    FROM CteMinMax m
    CROSSJOIN Tally t
    WHERE DATEADD(DAY, t.N-1, CAST(m.sdt ASDATE)) < DATEADD(DAY, 1, CAST(m.edt ASDATE))
)
SELECT
    t.col1,
    start_date =CASEWHEN t.start_date > d.sdt THEN t.start_date
            ELSECAST(d.sdt AS DATETIME)
        END,
    end_date =CASEWHEN t.end_date >= d.edt THENCAST(d.edt AS DATETIME)
            ELSE t.end_date
        ENDFROM yourTable t
INNERJOIN CteDates d
    ON d.sdt >=CAST(t.start_date ASDATE) 
    AND d.sdt < DATEADD(DAY, 1, CAST(t.end_date ASDATE))

SQL Fiddle

Solution 3:

Thanks Hart. Your logic worked with a little change.

SELECT col1
      ,CASEWHENCAST(start_dt ASDATE) = b.cal_dt THEN start_dt
            WHENCAST(end_dt ASDATE) = b.cal_dt THENCAST(CAST(end_dt ASDATE)AS DATETIME)
                ELSECAST(cal_dt AS DATETIME)
           ENDAS start_dt
           ,CASEWHENCAST(start_dt ASDATE) =CAST(end_dt ASDATE) THEN end_dt
            WHENCAST(start_dt ASDATE) = b.cal_dt THENCAST(DATEADD(day,1,CAST(start_dt ASDATE))AS DATETIME)
                    WHENCAST(end_dt ASDATE) = b.cal_dt THEN end_dt
                    ELSECAST(DATEADD(day,1,cal_dt) AS DATETIME)
                ENDAS end_dt
        FROM Table1 a
        JOIN lkp_Calendar b
          ON b.cal_dt BETWEENCAST(start_dt ASDATE) ANDCAST(end_Dt ASDATE)