Count Avg of last 36 months when months are in a wide format(cant change this format)

I have a weird snowflake table format which is causing issues when showing last 36 months of data only.

in words, i want to see last 36 months of data so when looking at current date, everything before May 2021 should show as 0.

The fiscal year starts from Oct hence the table months are shifted.

The years go further than 2021 but i only want last 36 months.

the platform is snowflake script but if you have a sql script i can translate to snowflake as well.

any advice would be appreciated.

Original Table im starting with:

Account	Year	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep
A1	2021	1	2	3	4	5	6	44	55	66	77	88	99
A2	2022	1	2	3	4	5	6	44	55	66	77	88	99
A3	2023	1	2	3	4	5	6	44	55	66	77	88	99
A4	2024	1	2	3	4	5	6

The result table im struggling to get:

Account	Year	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep
A1	2021	0	0	0	0	0	0	0	55	66	77	88	99
A2	2022	1	2	3	4	5	6	44	55	66	77	88	99
A3	2023	1	2	3	4	5	6	44	55	66	77	88	99
A4	2024	1	2	3	4	5	6	7

Solution

This could be done, by UNPIVOT then two filters and a rePIVOT,

OR given the columns are known and fixed at 12:

with cte_pretending_to_be_a_table(Account,Year,Oct,Nov,Dec,Jan,Feb,Mar,Apr,May,Jun, Jul, Aug, Sep) as (
    select * from values
    ('A-1', 2019, 1, 2, 3, 4, 5, 6, 44, 55, 66, 77, 88, 99),
    ('A0', 2020, 1, 2, 3, 4, 5, 6, 44, 55, 66, 77, 88, 99),
    ('A1', 2021, 1, 2, 3, 4, 5, 6, 44, 55, 66, 77, 88, 99),
    ('A2', 2022, 1, 2, 3, 4, 5, 6, 44, 55, 66, 77, 88, 99),
    ('A3', 2023, 1, 2, 3, 4, 5, 6, 44, 55, 66, 77, 88, 99),
    ('A4', 2024, 1, 2, 3, 4, 5, 6, null, null, null, null, null, null)
)
select 
    d.account, 
    d.year,
    date_trunc('month', dateadd('year',-3, current_date())) as m3y,
    iff(date_from_parts(d.year-1, 10, 1) >= m3y, oct, 0) as oct,
    iff(date_from_parts(d.year-1, 11, 1) >= m3y, nov, 0) as nov,
    iff(date_from_parts(d.year-1, 12, 1) >= m3y, dec, 0) as dec,
    iff(date_from_parts(d.year, 1, 1) >= m3y, jan, 0) as jan,
    /* ... */
    iff(date_from_parts(d.year, 5, 1) >= m3y, may, 0) as may,
    iff(date_from_parts(d.year, 6, 1) >= m3y, jun, 0) as jun,
    /* ... */
from cte_pretending_to_be_a_table as d
where year(current_date())-3 <= d.year
order by d.year
;

ACCOUNT	YEAR	M3Y	OCT	NOV	DEC	JAN	MAY	JUN
A1	2021	2021-05-01	0	0	0	0	55	66
A2	2022	2021-05-01	1	2	3	4	55	66
A3	2023	2021-05-01	1	2	3	4	55	66
A4	2024	2021-05-01	1	2	3	4