clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601504 fiddles created (47957 in the last week).

CREATE TABLE dbo.GLProject (project int, amount int, period date); INSERT dbo.GLProject VALUES (12345, 10, '20200101'), (12345, 10, '20200201'), (12345, 10, '20200301'), (12345, 10, '20200401'), (12345, 10, '20200501'), (12345, 10, '20200601'), (12345, 10, '20200901')/*, (23456, 20, '20210901')*/; DECLARE @d date = DATEFROMPARTS(YEAR(getdate()), MONTH(getdate()), 1); ;WITH src AS -- mostly your existing query ( SELECT project, period, rn = ROW_NUMBER() OVER (PARTITION BY project ORDER BY period DESC), amount = SUM(SUM(amount)) OVER (PARTITION BY project ORDER BY period ROWS UNBOUNDED PRECEDING) FROM dbo.GLProject GROUP BY project, period ), recur AS ( SELECT project, period = DATEADD(MONTH, 1, period), amount FROM src WHERE rn = 1 AND period < @d UNION ALL SELECT project, DATEADD(MONTH, 1, period), amount FROM recur WHERE period < @d ) SELECT project, amount, period = CONVERT(char(6), period, 112) FROM src UNION ALL SELECT project, amount, period = CONVERT(char(6), period, 112) FROM recur ORDER BY project, period;
project amount period
12345 10 202001
12345 20 202002
12345 30 202003
12345 40 202004
12345 50 202005
12345 60 202006
12345 70 202009
12345 70 202010
12345 70 202011
12345 70 202012
12345 70 202101
12345 70 202102
12345 70 202103
12345 70 202104
12345 70 202105
12345 70 202106
12345 70 202107
12345 70 202108
12345 70 202109
 hidden batch(es)