If I have a requirement to list the years from a start year incrementing to the current year without any gap years, I can write the following simple SELECT statement within a CTE.
WITH FINANCIAL_YEARS AS (
SELECT 2020 AS FINANCIAL_YEAR
UNION SELECT 2021
UNION SELECT 2022
UNION SELECT 2023
)
However its pretty easy to see how tedious this can be especially when trying to maintain it when a new year ticks over.
I found this neat trick using recursive CTEs to avoid gaps in dates and tweaked it to deal with year increments:
WITH FINANCIAL_YEARS AS (
SELECT '2020-01-01'::DATE AS YEAR_DATE
UNION ALL
SELECT DATEADD(YEAR, 1, YEAR_DATE) AS YEAR_DATE
FROM FINANCIAL_YEARS
WHERE YEAR_DATE < DATEADD(YEAR, -1, DATEADD(MONTH, 6, CURRENT_DATE())
)
SELECT DATE_PART(YEAR, YEAR_DATE) AS FINANCIAL_YEAR FROM FINANCIAL_YEARS
Its even simpler if you only want the year component or any other kind of numeric increment from a starting number:
WITH FINANCIAL_YEARS AS (
SELECT 2020 AS FINANCIAL_YEAR
UNION ALL
SELECT FINANCIAL_YEAR + 1 AS FINANCIAL_YEAR
FROM FINANCIAL_YEARS
WHERE FINANCIAL_YEAR < YEAR(DATEADD(MONTH, 6, CURRENT_DATE()))
)
SELECT FINANCIAL_YEAR FROM FINANCIAL_YEARS
No comments :
Post a Comment