SQL Server cursor type of run (without cursor?)

Question

given the following table:

create table xx (start_time datetime, end_time datetime, label varchar(100));
insert into xx values
('20180101 08:00', '20180103 08:00', 'test 1'), 
('20180101 06:30', '20180101 08:00', 'test 2'), 
('20180101 10:00', '20180102 08:00', 'test 3');

I have to generate a list where the records are duplicated as many times as I have days between start_time and end_time. The expected result is:

run_date    label
2018-01-01  test 1
2018-01-02  test 1
2018-01-03  test 1
2018-01-01  test 2
2018-01-01  test 3
2018-01-02  test 3

How can I achieve this in a performant way (maybe without any ugly cursor)? The time interval is undefined dynamic (between 1 and 10 days) The source table is quite big (some million records)

Answer 1

If you don't have a calendar table, one approach is an ad-hoc tally table in concert with a CROSS APPLY

Example

Select run_date=cast(B.D as date)
      ,A.label
 from XX A
 Cross Apply (
                Select Top (DateDiff(DAY,Start_Time,End_Time)+1) D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),Start_Time) 
                 From  master..spt_values n1 -- ,master..spt_values n2  -- remove comment if span > 6 years
             ) B

Returns

run_date    label
2018-01-01  test 1
2018-01-02  test 1
2018-01-03  test 1
2018-01-01  test 2
2018-01-01  test 3
2018-01-02  test 3

EDIT

Just Noticed your note of millions of records. You may be better off using a JOIN to a Calendar Table

Answer 2

This is one of the places when a Numbers table comes handy. Here an article of Aaron Bertrand talking about those tables.

Essentially, you create a table Numbers with a single column Number that has N rows, going from 0 (or 1) to the max number you want (and that the underlying type supports). Then you can JOIN easily:

SELECT CONVERT(date, xx.start_time + n.Number) AS RunningDate, xx.label
FROM xx
INNER JOIN Numbers n ON n.Number <= DATEDIFF(DAY, xx.start_time, xx.end_time)

This solution assumes that the Numbers table starts at 0. It should be slightly modified to allow the same for a table starting at 1.