How can I expand a table with a range between two dates as a new column in SQL Server?-CodePudding

I have this table for example:

Start date	End date	value
2022-01-01	2022-01-03	value1
2022-01-02	2022-01-04	value2

The output I want would be this:

Start date	End date	value	Date between
2022-01-01	2022-01-03	value1	2022-01-01
2022-01-01	2022-01-03	value1	2022-01-02
2022-01-01	2022-01-03	value1	2022-01-03
2022-01-02	2022-01-04	value2	2022-01-02
2022-01-02	2022-01-04	value2	2022-01-03
2022-01-02	2022-01-04	value2	2022-01-04

Thank you in advance!

CodePudding user response：

As already suggested, you need a calendar table.
Here is how you can create one

create table calendar (id int identity, cdate date not null)

and fill it one time like this (choose a enddate far enough in the future, and a startdate far enough in the past so you won't have to add rows to this table anymore

;WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT 0 AS I
    UNION ALL                 --startdate  enddate
    SELECT TOP (DATEDIFF(DAY, '20220101', '20220301')) 
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3), --up to 1,000 days
Dates AS(
    SELECT DATEADD(DAY, T.I, '20220101') AS Date
    FROM Tally T)
insert into calendar (cdate)    
SELECT D.Date
FROM Dates D

So now you have a table called calendar where you can join on, the query you need is now very simple

select t.startdate,
       t.enddate,
       t.value,
       c.cdate as datebetween
from   mytable t
  left join calendar c on c.cdate >= t.startdate
                      and c.cdate <= t.enddate

Click on this DBFiddle to see how it works