CODEWITHSUNDEEP
sql-server-2008datetimedate-range
HHungria
HHungria

Reputation: 511

Insert Date Range with multiple Id SQL Server 2008

I have a date range defined by @starDate and @EndDate. I need insert the date range to each userId into a table.

For example I have the two dates 

 @StarDate = '2012/05/02' 
 @EndDate = '2012/05/06'

and the userIds: 1 - 2 - 3

I want these results

UserID      Date
------------------------        
1     -     2012/05/02  
1      -    2012/05/03  
1       -   2012/05/04  
1       -   2012/05/05  
1       -   2012/05/06  
2       -   2012/05/02  
2       -   2012/05/03  
2       -   2012/05/04  
2       -   2012/05/05  
2       -   2012/05/06  
3       -   2012/05/02  
3       -   2012/05/03  
3       -   2012/05/04  
3       -   2012/05/05  
3       -   2012/05/06

Upvotes: 0

Views: 102

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

One way is to use a recursive CTE:

with dates as (
      select @StartDate as dte
      union all
      select dateadd(day, 1, @StartDate)
      from dates
      where dateadd(day, 1, @StartDate) <= @EndDate
     ),
     users as (
      select 1 as userid union all select 2 union all select 3
     )
select users.userid, dates.dte
from users cross join dates;

You can do an insert the same way:

with dates as (
      select @StartDate as dte
      union all
      select dateadd(day, 1, @StartDate)
      from dates
      where dateadd(day, 1, @StartDate) <= @EndDate
     ),
     users as (
      select 1 as userid union all select 2 union all select 3
     )
insert into table2(userid, date)
    select users.userid, dates.dte
    from users cross join dates;

Note: this assumes that the start and end date variables are really stored as dates. Otherwise you need to convert them.

Upvotes: 1

Related Questions