Detect if a month is missing and insert them automatically with a select statement (MSSQL)

Question

I am trying to write a select statement which detects if a month is not existent and automatically inserts that month with a value 0. It should insert all missing months from the first entry to the last entry.

Example: My table looks like this:

After the statement it should look like this:

Answer 1

You need a recursive CTE to get all the years in the table (and the missing ones if any) and another one to get all the month numbers 1-12.
A CROSS join of these CTEs will be joined with a LEFT join to the table and finally filtered so that rows prior to the first year/month and later of the last year/month are left out:

WITH
  limits AS (
    SELECT MIN(year) min_year, -- min year in the table
           MAX(year) max_year, -- max year in the table
           MIN(DATEFROMPARTS(year, monthnum, 1)) min_date, -- min date in the table 
           MAX(DATEFROMPARTS(year, monthnum, 1)) max_date  -- max date in the table
    FROM tablename
   ),
  years(year) AS ( -- recursive CTE to get all the years of the table (and the missing ones if any)
    SELECT min_year FROM limits
    UNION ALL
    SELECT year + 1 
    FROM years
    WHERE year < (SELECT max_year FROM limits)
  ),  
  months(monthnum) AS ( -- recursive CTE to get all the month numbers 1-12
    SELECT 1 
    UNION ALL
    SELECT monthnum + 1
    FROM months
    WHERE monthnum < 12
  )
SELECT y.year, m.monthnum,
       DATENAME(MONTH, DATEFROMPARTS(y.year, m.monthnum, 1)) month,
       COALESCE(value, 0) value
FROM months m CROSS JOIN years y
LEFT JOIN tablename t 
ON t.year = y.year AND t.monthnum = m.monthnum
WHERE DATEFROMPARTS(y.year, m.monthnum, 1) 
      BETWEEN (SELECT min_date FROM limits) AND (SELECT max_date FROM limits)
ORDER BY y.year, m.monthnum 

See the demo.