-2

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

1 Answer 1

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.