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. 3601588 fiddles created (47999 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, 24, '20200901')/*, (23456, 31, '20200101'), (23456, 55, '20201101')*/; DECLARE @d date = DATEFROMPARTS(YEAR(getdate()), MONTH(getdate()), 1); ;WITH total_range AS ( SELECT project, period = MIN(period) FROM dbo.GLProject GROUP BY project UNION ALL SELECT project, DATEADD(MONTH, 1, period) FROM total_range WHERE period < @d ) SELECT tr.project, amount = SUM(COALESCE(p.amount,0)) OVER (PARTITION BY tr.project ORDER BY tr.period), period = CONVERT(char(6), tr.period, 112) FROM total_range AS tr LEFT OUTER JOIN dbo.GLProject AS p ON tr.project = p.project AND tr.period = p.period; ;WITH total_range AS ( SELECT project, period = MIN(period) FROM dbo.GLProject GROUP BY project UNION ALL SELECT project, DATEADD(MONTH, 1, period) FROM total_range WHERE period < @d ) SELECT tr.project, amount = SUM(COALESCE(p.amount,0)) OVER (PARTITION BY tr.project ORDER BY tr.period ROWS UNBOUNDED PRECEDING), period = CONVERT(char(6), tr.period, 112) FROM total_range AS tr LEFT OUTER JOIN dbo.GLProject AS p ON tr.project = p.project AND tr.period = p.period;
project amount period
12345 10 202001
12345 20 202002
12345 30 202003
12345 40 202004
12345 50 202005
12345 60 202006
12345 60 202007
12345 60 202008
12345 84 202009
12345 84 202010
12345 84 202011
12345 84 202012
12345 84 202101
12345 84 202102
12345 84 202103
12345 84 202104
12345 84 202105
12345 84 202106
12345 84 202107
12345 84 202108
12345 84 202109
12345 84 202110
12345 84 202111
project amount period
12345 10 202001
12345 20 202002
12345 30 202003
12345 40 202004
12345 50 202005
12345 60 202006
12345 60 202007
12345 60 202008
12345 84 202009
12345 84 202010
12345 84 202011
12345 84 202012
12345 84 202101
12345 84 202102
12345 84 202103
12345 84 202104
12345 84 202105
12345 84 202106
12345 84 202107
12345 84 202108
12345 84 202109
12345 84 202110
12345 84 202111
 hidden batch(es)