Fill down if no value in period, autoknow the periods

Question

I have created a query that gets the running total from my general ledger by period. This works fine, however as soon as there are no transasctions in a period, my query will return noting in that period. I would like to add previous value if this is the case. I have tried joining with my dimension period table, but have not been succesful.

Example: Project 12345 GL:

project	amount	period
12345	10	202001
12345	10	202002
12345	10	202003
12345	10	202004
12345	10	202005
12345	10	202006

My code is the following:

 select project, Period,
 Sum( Sum (amount))
 over (
 partition by project
 order by Period
 ROWS UNBOUNDED PRECEDING) AS [RTamt]
from GL.table
group by project, period

This would return the following table

project	amount	period
12345	10	202001
12345	20	202002
12345	30	202003
12345	40	202004
12345	50	202005
12345	60	202006

I would like it to continue until today period like this:

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	60	202009
12345	60	202010
12345	60	202011
12345	60	202012
12345	60	202101
12345	60	202102
12345	60	202103
12345	60	202104
12345	60	202105
12345	60	202106
12345	60	202107
12345	60	202108
12345	60	202109

Is there an easy way to do this by joining a period table maybe?

Thanks so much!

Answer 1

You can create a recursive CTE based on the highest period for any given project, then use a union to piece those two sets together. There is probably a slightly cleaner way to do this but I wanted to do as little to your existing query as possible (I just added a row number and slight formatting changes for my own readability).

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;

Working db<>fiddle