SQL Server: Generate rows for missing years within range (system year + 9)

Question

I have an SQL Server 2019 table that has rows for certain years:

with data (year_, amount) as (
select 2024, 100 union all
select 2025, 200 union all
select 2025, 300 union all
select 2026, 400 union all
select 2027, 500 union all
select 2028, 600 union all
select 2028, 700 union all
select 2028, 800 union all
select 2029, 900 union all
select 2031, 100
)
select * from data

     YEAR_     AMOUNT
---------- ----------
      2024        100
      2025        200
      2025        300
      2026        400
      2027        500
      2028        600
      2028        700
      2028        800
      2029        900
      2031        100

db<>fiddle

---

I want at least one row for each year within this range: system year + 9. In other words, I want rows for 10 years, starting with the current year (currently 2023).

However, I'm missing rows for certain years: 2023, 2030, and 2032. So I want to generate filler rows for those missing years. The amount for the filler rows would be null.

It would look like this:

     YEAR_     AMOUNT
---------- ----------
      2023             --filler
      2024        100
      2025        200
      2025        300
      2026        400
      2027        500
      2028        600
      2028        700
      2028        800
      2029        900
      2030             --filler
      2031        100
      2032             --filler

In an SQL Server 2019 query, how can I select the rows and generate filler rows within the 10 year range?

Edit: I would prefer not to manually create a list of years in the query or in a table. I would rather create a dynamic range within the query.

Answer 1

This should do:

with data (year_, amount) as (
    select 2024, 100 union all
    select 2025, 200 union all
    select 2025, 300 union all
    select 2026, 400 union all
    select 2027, 500 union all
    select 2028, 600 union all
    select 2028, 700 union all
    select 2028, 800 union all
    select 2029, 900 union all
    select 2031, 100
),
calendar AS (
    SELECT YEAR(GETDATE()) + offset AS YEAR_
    FROM (
        VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
    ) AS v(offset)
) 
select * 
from calendar AS c
LEFT JOIN data AS d
    ON c.YEAR_ = d.year_

How it works: you have a "calendar" table generated on the fly as a CTE, with only ten rows (years from now to +9 years). That CTE is joined to your data on the year column.