How to repeat the values between the two dates?

Question

Using SQL Server 2005

I want to generate the table values between the two dates

Table1

ID Date Intime Outtime

01 12-02-2009 10:00:00 17:00:00
02 13-02-2009 08:00:00 16:00:00
03 14-02-2009 09:00:00 21:00:00
04 15-02-2009

Suppose I want to generate the above table values between the two dates.

For Example,

Given Date: start date - 12-02-2009 to end date - 12-03-2009

Expected Output

ID Date Intime Outtime

01 12-02-2009 10:00:00 17:00:00
02 13-02-2009 08:00:00 16:00:00
03 14-02-2009 09:00:00 21:00:00
04 15-02-2009 
05 16-02-2009 10:00:00 17:00:00
06 17-02-2009 08:00:00 16:00:00
07 18-02-2009 09:00:00 21:00:00
08 19-02-2009 
09 20-02-2009 10:00:00 17:00:00
…,

So From the table1 we have 4 Rows, so the 4 rows will repeat up to the given end date but id and date should increment, id and date should not repeat

How to make a SQL Query for this condition?

Need Query Help.

Answer 1

Try this

declare @startdate datetime
declare @enddate datetime

set @startdate = '12-02-2009'
set @enddate = '12-05-2009'

;with generateCalender_cte as
(
select  
1 as Id
,@startdate DateValue
union all
select 
c.Id + 1      
    ,DateValue + 1
from    generateCalender_cte c  
where   DateValue + 1 <= @enddate
)

select * from generateCalender_cte

Answer 2

Another solution

 declare @startdate datetime
    declare @enddate datetime

    set @startdate = '12-02-2009'
    set @enddate = '12-05-2009'

    ; with generateCalender_cte as
    (
          select 
          1 as Id
          ,@startdate as newdate
          union all
          select 
          c.Id + 1
          ,DATEADD(day,1,c.newdate)
          from generateCalender_cte c 
          where c.newdate <@enddate
     )
     select * from generateCalender_cte

Answer 3

Try this too...

 declare @startdate datetime
    declare @enddate datetime

    set @startdate = '12-02-2009'
    set @enddate = '12-05-2009'

    ;with num_cte as
    (
          select top(datediff(day,@startdate,@enddate)) ROW_NUMBER() OVER (ORDER BY c.column_id) AS rn
          from sys.columns c
    )
    , generate_date_cte as
    ( 
          select 1 as Id,@startdate as newdate 
          union all
          select rn+1 as Id, newdate
          from num_cte
          cross apply( select DATEADD(day,rn,@startdate) AS newdate) x
    )
     select * from generate_date_cte