I have an SQLite 3.38.2 table that has rows for certain years:
with data (year_, amount) as (
values
(2024, 100),
(2025, 200),
(2025, 300),
(2026, 400),
(2027, 500),
(2028, 600),
(2028, 700),
(2028, 800),
(2029, 900),
(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 |
Here's the Demo.
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 SQLite 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.
CodePudding user response:
You may use a calendar table approach, which will require keeping a table containing all years which you want to appear in your report. Left join this calendar table to your current table to get the result you want.
WITH years AS (
SELECT 2023 AS YEAR_ UNION ALL
SELECT 2024 UNION ALL
...
SELECT 2040
)
SELECT y.YEAR_, d.AMOUNT
FROM years y
LEFT JOIN data d
ON d.YEAR_ = y.YEAR_
WHERE d.YEAR_ BETWEEN SYSYEAR AND SYSYEAR 9
ORDER BY d.YEAR_;
Note that in practice, rather than using the above CTE years
, you may create a bona fide table containing the next hundred years in it.
CodePudding user response:
Yet another option is using a recursive approach to generate your N dates:
WITH RECURSIVE cte AS (
SELECT 2023 AS year_
UNION ALL
SELECT year_ 1
FROM cte
WHERE year_ < 2023 (10) -1
)
SELECT cte.year_, data.amount
FROM cte
LEFT JOIN data
ON cte.year_ = data.year_
Check the demo here.