Spread a table in a date time interval

Question

Hello everyone it's been some days that I use sql to make analysis and I meet all kinds of problems that I solves thanks to your forum.

Now I'd like to create a view that recuperates the interval of time and shows in detail the dates in this interval.

I have the following table:

And I want to create the view that displays the result:

For example in the player1 MyTable to play five days from 01/01/2012 to 05/01/2012. So the view displays 5 lines for player1 with the date 01/01/2012, 02/01/2012, 03/01/2012, 04/01/2012, 05/01/2012.

Thank you in advance for your help.

Answer 1

You have to create a common table expression that give you the date range ( i have created a date range of the current month but you can choice another range) :

WITH DateRange(dt) AS
(
SELECT CONVERT(datetime, '2012-01-01') dt
UNION ALL
SELECT DATEADD(dd,1,dt) dt FROM DateRange WHERE dt < CONVERT(datetime, '2012-01-31')
)

SELECT dates.dt AS DatePlaying, PlayerName 
FROM MyTable t
JOIN DateRange dates ON dt BETWEEN t.BeginDate AND t.DateEnd
ORDER BY PlayerName, DatePlaying