CODEWITHSUNDEEP
snowflake-cloud-data-platform
Pandafreak
Pandafreak

Reputation: 107

Snowflake filtering for next fiscal quarter

I've got this date column (closing_date) with fields that look like this e.g. 2023-01-20 How do I dynamically filter for only records where the date is in the next fiscal quarter?

Closing_date Name Order_id
2024-01-20 Joe 123
2023-01-20 Joe 456

Only the second record in the table should pull in because this fiscal quarter and year ends at the end of January. Can I do this w/o having to update my date filter each quarter?

Upvotes: 1

Views: 456

Answers (2)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25928

So if we define a variable to state the first month of a finacal year, here in New Zealand it's 1st April:

[corrected: to do the cur_date_projected of current date]

set fin_month = 4; // April is the first day on NZ fin year

with data(Closing_date, Name, Order_id) as (
    select * from values 
    ('2024-01-20'::date,    'Joe',  123),
    ('2023-01-20'::date,    'Joe',  456)
)
select *
    ,dateadd(month,(-$fin_month)+1, Closing_date) as date_projected
    ,date_trunc('quarter', date_projected) as date_in_fin_years
    ,year(date_in_fin_years) || 'Q' || quarter(date_in_fin_years) as date_as_fin_str

    ,dateadd(month,-$fin_month+1, CURRENT_DATE) as cur_date_projected
    ,dateadd('quarter', 1, date_trunc('quarter', cur_date_projected)) as cur_date_in_fin_years
    ,year(cur_date_in_fin_years) || 'Q' || quarter(cur_date_in_fin_years) as cur_date_as_fin_str
from data

gives:

CLOSING_DATE NAME ORDER_ID DATE_PROJECTED DATE_IN_FIN_YEARS DATE_AS_FIN_STR CUR_DATE_PROJECTED CUR_DATE_IN_FIN_YEARS CUR_DATE_AS_FIN_STR
2024-01-20 Joe 123 2023-10-20 2023-10-01 2023Q4 2022-07-10 2022-10-01 2022Q4
2023-01-20 Joe 456 2022-10-20 2022-10-01 2022Q4 2022-07-10 2022-10-01 2022Q4

so if we swap to fin_month to 1, and then put those date_projected and cur_date_projected into a WHERE clause

select *
from data
where date_trunc('quarter', dateadd(month,(-$fin_month)+1, Closing_date)) = dateadd('quarter', 1, date_trunc('quarter', dateadd(month,-$fin_month+1, CURRENT_DATE)))

gives:

CLOSING_DATE NAME ORDER_ID
2023-01-20 Joe 456

Upvotes: 0

JNevill
JNevill

Reputation: 50034

Some date math should do the trick:

WHERE YEAR(ADD_MONTHS(CURRENT_DATE(), 3))*10 + QUARTER(ADD_MONTHS(CURRENT_DATE, 3)) = YEAR(Closing_date)*10 + QUARTER(Closing_date)

Upvotes: 0

Related Questions