CODEWITHSUNDEEP
sqlsql-server
Kutay yorgun
Kutay yorgun

Reputation: 13

How can i Insert all dates between start and end date into table?

let's say i have 2 table

First one is "Orders"

Select * from Orders

give me this results.

 Order_ID   Date_Start          Date_End                                   Order_Name
2059    2020-11-13 00:00:00.000 2020-11-14 00:00:00.000             order1
2060    2020-12-12 00:00:00.000 2020-12-22 00:00:00.000             order2

and second table say it "Dates"

This is desired results for Dates table.i need to insert dates between two dates to that table for each order ID.

Date                    Type1   Type2   Type3   Type4   Type5   Order_ID
2020-11-13 00:00:00.000 NULL    NULL    NULL    NULL    NULL    2059
2020-11-14 00:00:00.000 NULL    NULL    NULL    NULL    NULL    2059

Upvotes: 0

Views: 452

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you need to generate data for each date for each order, use a recursive CTE:

with cte as (
      select orderid, date_start as date, date_end
      from orders
      union all
      select orderid, dateadd(day, 1, date), date_end
      from cte
      where date < date_end
     )
select *
from cte;

You can also use a recursive CTE in an insert statement:

with cte as (
      select orderid, date_start as date, date_end
      from orders
      union all
      select orderid, dateadd(day, 1, date), date_end
      from cte
      where date < date_end
     )
insert into dates (date, order_id)
    select date, order_id
    from cte;

Upvotes: 3

Related Questions