fill in missing data gaps in a table

Question

I'm looking to fill in some missing data gaps caused by missing reporting_month date. I would like to fill the data gaps with the previous recorded values.

Below is the Sample I have with the 2,3,4 & 6 months missing.

DECLARE @test TABLE
    (reporting_year datetime,
     repoting_month integer,
     Value integer) 

    INSERT  INTO @test
    VALUES  
    ('2022',    1,  3),
    ('2022',    5,  4),
    ('2022',    7,  4),
    ('2022',    8,  5),
    ('2022',    9,  5),
    ('2022',    10, 5);

Below are the expected values with the additional rows populated from the previous records.

DECLARE @required TABLE
    (reporting_year datetime,
     repoting_month integer,
     Value integer) 

    INSERT  INTO @required
    VALUES  
    ('2022',    1,  3),
    ('2022',    2,  3),
    ('2022',    3,  3),
    ('2022',    4,  3),
    ('2022',    5,  4),
    ('2022',    6,  4),
    ('2022',    7,  4),
    ('2022',    8,  5),
    ('2022',    9,  5),
    ('2022',    10, 5);

Answer 1

You can use GENERATE_SERIES to generate more rows for each one that has missing rows following it.

You can find the next row's value using LEAD.

WITH nextValues AS (
    SELECT *,
      LEAD(repoting_month) OVER (PARTITION BY reporting_year ORDER BY repoting_month) AS nextMonth
    FROM @test t
)
SELECT
  t.reporting_year,
  t.repoting_month + ISNULL(g.value, 0),
  t.Value
FROM nextValues t
OUTER APPLY GENERATE_SERIES(0, t.nextMonth - t.repoting_month - 1) g;

In older versions of SQL Server, you need a numbers generator.

WITH nextValues AS (
    SELECT *,
      LEAD(repoting_month) OVER (PARTITION BY reporting_year ORDER BY repoting_month) AS nextMonth
    FROM @test t
)
SELECT
  t.reporting_year,
  t.repoting_month + ISNULL(g.value, 0),
  t.Value
FROM nextValues t
OUTER APPLY dbo.GetNums(0, ISNULL(t.nextMonth - t.repoting_month - 1, 0)) g;

db<>fiddle