How to aggregate data for non-working dates?

Question

I have two tables. The table1 has working dates, and table2 contains a numeric Value for calendar dates.
I need to join those tables and output the Value from table2 with the dates of table1.

Tricky part is that during the non-working date(s) the Value should aggregate and add to the Value of next working date.

Here is simple script to create a sample data of this scenario.

CREATE TABLE #table1
(
    [Date] DATE PRIMARY KEY
)

INSERT INTO #table1 VALUES 
('2021-02-12'),
('2021-02-15'),
('2021-02-16'),
('2021-02-17'),
('2021-02-18'),
('2021-02-19'),
('2021-02-22'),
('2021-02-23'),
('2021-02-24')

CREATE TABLE #table2
(
    [Date] DATE PRIMARY KEY,
    [Value] INT NOT NULL
)

INSERT INTO #table2 VALUES 
('2021-02-12', 1),
('2021-02-13', 1),
('2021-02-14', 2),
('2021-02-15', 3),
('2021-02-16', 5),
('2021-02-17', 8),
('2021-02-18', 13),
('2021-02-19', 21),
('2021-02-20', 34),
('2021-02-21', 55),
('2021-02-22', 89),
('2021-02-23', 144),
('2021-02-24', 233)
GO

This is how output should look like:

 ---------------------
 | Date       |Value |
 | ------------------|
 | 2021-02-12 | 1    |
 | 2021-02-15 | 6    |
 | 2021-02-16 | 5    |
 | 2021-02-17 | 8    |
 | 2021-02-18 | 13   |
 | 2021-02-19 | 21   |
 | 2021-02-22 | 178  |
 | 2021-02-23 | 144  |
 | 2021-02-24 | 233  |
 ---------------------

I tried following:

WITH x AS
(
    SELECT
         ROW_NUMBER() OVER (ORDER BY [Date]) AS RowID
        ,[Date]
    FROM #table1
)
SELECT 
    t1.[Date]
    ,(
        SELECT SUM([Value])
        FROM #table2
        WHERE [Date] > ISNULL(t1p.[Date], '00010101') AND [Date] <= t1.[Date]
     ) AS [Value]
FROM x t1
LEFT OUTER JOIN x t1p
ON t1.RowID = t1p.RowID + 1
ORDER BY
    t1.[Date]
GO

This is working and produce what I need but it is painfully slow (not on this sample but on the actual data).

Any idea how can I optimize this by grouping or some other technique to make it faster would be appreciated.

user_5176071 · Answer

You can try to use SUM window function with your condition to make a group number, then aggregate data for non-working dates.

;WITH CTE AS (
    SELECT t2.[Date],
           t2.[Value],
          SUM(CASE WHEN t1.[Date] IS NOT NULL Then 1 END) OVER(ORDER BY t2.[Date] DESC) grp
    FROM #table2 t2 
    LEFT JOIN #table1 t1
    ON t1.[Date] = t2.[Date]
)
SELECT MAX([Date]) as 'Date',
       SUM([Value]) as 'Value'
FROM CTE 
GROUP BY grp
ORDER BY [Date]

sqlfiddle

How to aggregate data for non-working dates?

1 Answers