Converting 1 record with a start and end date into multiple records for each day

Question

I'd like to know an efficient way of taking event records with a start date and end date and basically replicating that record for each day between the start and end date

So a record with a Start Date as 2014-01-01 and End Date as 2014-01-03 would become 3 records, one for each day

I have a date table if that helps. I'm using SQL Server 2012

Thanks

Answer 1

As you already have date table, You can JOIN your table with date table to get all dates to have same record as your start and end date

 SELECT A.data,
    DT.startDate,
    DT.endDate
 FROM
 DateTable DT
 JOIN A
 ON A.StartDate >= DT.startDate
 And A.EndDate <= DT.endDate

Answer 2

use this query

declare @startDate datetime = getdate()
declare @endDate datetime = dateadd(day,10,getdate())


;with days as 
(
    select
        @startDate as StartDate,
        @endDate as EndDate,
        @startDate as CurrentDate,
        0 as i
    union all
    select 
        d.StartDate,
        d.EndDate,
        dateadd(day,d.i + 1,@startDate) as CurrentDate,
        d.i + 1 as i
    from days d
    where dateadd(day,d.i + 1,@startDate) < d.EndDate
)
select
*
from days d