Fill in missing periods in between periods where missing transactions

Question

With a lof of help from the community I have been able to create a query that automatically adds new rows for project with accumulated number from previous row if there are no transactions. Unfortunately I came across on thing I would like to change. If there are no transactions in periods in between periods, those periods will be missing. I would like to fill those as well.

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
12345	10	202009

My code is the following:

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;

I would like it to fill in the missing periods 202007 and 202008 as well. 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	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

Any ideas on what to add to my query to solve this?

Fiddle for testing here: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=615712cf89113c3a912268ed603fd2b4

Thanks so much!

Answer 1

You probably won't believe me, but adding the requirement of filling in missing gaps actually drove a simpler solution.

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
             -- ROWS UNBOUNDED PRECEDING -- not strictly necessary here
           ), 
  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;

• Example: db<>fiddle