And the recursive:

DECLARE @mindate Date, @Maxdate Date
SELECT @mindate = MIN(StartDate), @Maxdate = MAX(EndDate) FROM SourceData ;

WITH Dates AS (
        SELECT Date = @mindate
        UNION ALL 
		SELECT Date = DATEADD(DAY, 1, Date) FROM Dates WHERE Date < @maxDate
) 
SELECT Name, Date FROM Sourcedata INNER JOIN DATES ON date between startdate AND enddate OPTION (MAXRECURSION 32635)

The bare bone approach from my previous post seems faster and I prefer that one as I really hate CTE's despite their obvious strenght.
In most cases there is a viable alternative that is both faster and easier to understand.

Walter,




>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