SQL breakout date range to rows

Question

I am trying to take given date ranges found in a data set and divide them into unique rows for each day in the range (example below). Doing the opposite in SQL is pretty straight forward, but I am struggling to achieve the desired query output.

Beginning data:

ITEM    START_DATE  END_DATE
A       1/1/2015    1/5/2015
B       2/5/2015    2/7/2015

Desired query output:

 ITEM   DATE_COVERED
 A      1/1/2015
 A      1/2/2015
 A      1/3/2015
 A      1/4/2015
 A      1/5/2015
 B      2/5/2015
 B      2/6/2015
 B      2/7/2015

Answer 1

The fastest way will be some tally table:

DECLARE @t TABLE
    (
      ITEM CHAR(1) ,
      START_DATE DATE ,
      END_DATE DATE
    )
INSERT  INTO @t
VALUES  ( 'A', '1/1/2015', '1/5/2015' ),
        ( 'B', '2/5/2015', '2/7/2015' )



;WITH cte AS(SELECT -1 + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) d FROM
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t1(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t2(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t3(n) CROSS JOIN
(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t4(n))

SELECT t.ITEM, ca.DATE_COVERED FROM @t t
CROSS APPLY(SELECT DATEADD(dd, d, t.START_DATE) AS DATE_COVERED 
            FROM cte 
            WHERE DATEADD(dd, d, t.START_DATE) BETWEEN t.START_DATE AND t.END_DATE) ca   
ORDER BY t.ITEM, ca.DATE_COVERED

Answer 2

Query:

SQLFiddleExample

SELECT t.ITEM, 
        DATEADD(day,n.number, t.START_DATE) AS DATE_COVERED
FROM Table1 t,
(SELECT number
FROM master..spt_values
WHERE [type] = 'P') n
WHERE START_DATE <= DATEADD(day,n.number, t.START_DATE)
AND END_DATE >= DATEADD(day,n.number, t.START_DATE)

Result:

| ITEM | DATE_COVERED |
|------|--------------|
|    A |   2015-01-01 |
|    A |   2015-01-02 |
|    A |   2015-01-03 |
|    A |   2015-01-04 |
|    A |   2015-01-05 |
|    B |   2015-02-05 |
|    B |   2015-02-06 |
|    B |   2015-02-07 |

Answer 3

NOTE: this only works if the difference between your startdate and enddate is a maximum of 2047 days (master..spt_values only allows 0..2047 range of values)

 select item, dateadd(d,v.number,d.start_date) adate
 from begindata d
 join master..spt_values v on v.type='P'
           and v.number between 0 and datediff(d, start_date, end_date)
 order by adate;

I'd like to say I did this myself but I got the code from this

Here is a fiddle with your expected result

Answer 4

TRY THIS...

CREATE TABLE Table1
    ([ITEM] varchar(1), [START_DATE] date, [END_DATE] date)
;

INSERT INTO Table1
    ([ITEM], [START_DATE], [END_DATE])
VALUES    ('A', '2015-01-01', '2015-01-05'), ('B', '2015-02-05', 2015-02-07');

WITH    Days
          AS ( SELECT ITEM,  START_DATE AS [Date], 1 AS [level] from Table1
               UNION ALL
               SELECT  TABLE1.ITEM, DATEADD(DAY, 1, [Date]), [level] + 1
               FROM     Days,Table1
               WHERE  DAYS.ITEM=TABLE1.ITEM AND  [Date] < END_DATE )
     SELECT distinct [Date]
     FROM   Days

DEMO