How to Get all Dates Between Two Dates in SQL

Question

I want to display all the dates between two dates for a particular record

Table.

JWDID	FromDate	ToDate	Quantity
100	2022-06-01	2022-06-05	1000
101	2022-06-03	2022-06-03	1500
102	2022-06-29	2022-07-03	1800

I need to output all dates with Quantity between FromDate & ToDate.

Where FromDate >= '2022-06-01' AND ToDate <= '2022-06-30'

Expected output :

JWDID	Date	FromDate	ToDate	Quantity
100	2022-06-01	2022-06-01	2022-06-05	1000
100	2022-06-02	2022-06-01	2022-06-05	1000
100	2022-06-03	2022-06-01	2022-06-05	1000
100	2022-06-04	2022-06-01	2022-06-05	1000
100	2022-06-05	2022-06-01	2022-06-05	1000
101	2022-06-03	2022-06-03	2022-06-03	1500

guide me to write a query

Answer 1

The key point here, is to have an "utility" table with numbers from 0 to a number (like 1000 or 10000, look for tally table on google : 1 , 2 ) and use it to join base on DATEADD(DAY, y.number ,T.FromDate)

CREATE TABLE #Table1
([JWDID] int, [FromDate] date, [ToDate] date, [Quantity] int)
;
    
INSERT INTO #Table1
    ([JWDID], [FromDate], [ToDate], [Quantity])
VALUES
    (100, '2022-06-01', '2022-06-05', 1000),
    (101, '2022-06-03', '2022-06-03', 1500),
    (102, '2022-06-29', '2022-07-03', 1800)
;    

WITH cteTally AS
(
    SELECT TOP(100) v.number
    FROM master.dbo.spt_values as v
    WHERE v.type = 'P'
    ORDER BY v.number ASC
)
SELECT  
    t.JWDID, 
    [DATE] = CAST(DATEADD(DAY,y.number,T.FromDate) AS DATE),
    FromDate = t.FromDate,
    ToDate = t.ToDate,
    t.Quantity
FROM #Table1 as t
    INNER JOIN cteTally as y
    ON t.ToDate > = DATEADD(DAY,y.number,T.FromDate)
;

The output:

JWDID	DATE	FromDate	ToDate	Quantity
100	2022-06-01	2022-06-01	2022-06-05	1000
100	2022-06-02	2022-06-01	2022-06-05	1000
100	2022-06-03	2022-06-01	2022-06-05	1000
100	2022-06-04	2022-06-01	2022-06-05	1000
100	2022-06-05	2022-06-01	2022-06-05	1000
101	2022-06-03	2022-06-03	2022-06-03	1500
102	2022-06-29	2022-06-29	2022-07-03	1800
102	2022-06-30	2022-06-29	2022-07-03	1800
102	2022-07-01	2022-06-29	2022-07-03	1800
102	2022-07-02	2022-06-29	2022-07-03	1800
102	2022-07-03	2022-06-29	2022-07-03	1800

dbfiddle here