Haven't done one of these in a while.

Recently someone asked me about exploding out a date range by user.

For instance, suppose you had the following table definition and two sample rows:

create table dbo.SourceData (Name varchar(100), StartDate date, EndDate date)	

insert into dbo.SourceData values
    ('Kevin Goff', '9-12-2016', '9-12-2016'), 
    ('Katy Goff','9-12-2016', '9-15-2016') 

So I have a date range of 9-12 to 9-12 (one day), and Katy has a date range of 9-12 to 9-15 (4 days).

I want to generate a result set of 5 rows...one for me and four for Katy, with the corresponding dates. In reality, the sourceData table could have thousands of rows.

Absolute best performance isn't paramount, since this routine would only run once a day, but obviously it could run more frequently down the road.

Here was my solution...I've had to do this before to populate data marts. I know that recursion allows me to generate a range, but in order to use a CROSS APPLY to apply the routine across many rows, I've only ever been able to do it with a table function.

So here was my solution:

CREATE FUNCTION dbo.ExplodeDates
( @StartDate date, @EndDate date)
returns table
as 
return (
    with tempcte as
                (select @StartDate as ActualDate
                      union all             -- CTE recursive loop
                   select dateadd(d,1,ActualDate) from tempcte
                           where dateadd(d,1,ActualDate) <= @EndDate )
        select * from tempcte )

go


select Name, ActualDate from SourceData
   CROSS APPLY dbo.ExplodeDates ( SourceData.StartDate, SourceData.EndDate)
        OPTION (MAXRECURSION 32767)

This works, and generates the following results:

Name	     ActualDate
Kevin Goff       2016-09-12
Katy Goff       2016-09-12
Katy Goff       2016-09-13
Katy Goff       2016-09-14
Katy Goff       2016-09-15

Again, this works - though it doesn't seem 100% optimal. Admittedly, I've never spent any serious time thinking about ways to optimize it. But just curious if anyone had any thoughts.

Thanks