How to get the data in snowflake for past 24 months in snowflake

Question

I have the data for the current month in Snowflake which I am extracting with the below mentioned query

select distinct HPOLICY
              , ANNUALPREMIUMAMOUNT
              , year(dateadd(year, 0, CURRENT_DATE))
              , month(dateadd(month, 0, CURRENT_DATE)) yearmonth
from hub_test

I want to extrapolate this data to the past 24 months which means get the same data with Sep 2019, Aug 2019 and so on till past 24 months.

Answer 1

That query is get all time distinct data, and put a fake current year/month column on it.

If you where doing something like:

select distinct HPOLICY
    ,ANNUALPREMIUMAMOUNT
    ,date_part('year', date_column) as year
    ,date_part('month', date_column) as month 
from hub_test
where date_column >= date_trunc('month',CURRENT_DATE());

you would have the current months data, if date_column was the date_of the data in the row.

Therefore to get the last 24 months you would alter that to:

select distinct HPOLICY
    ,ANNUALPREMIUMAMOUNT
    ,date_part('year', date_column) as year
    ,date_part('month', date_column) as month 
from hub_test
where date_column >= dateadd('month',-24, date_trunc('month',CURRENT_DATE()));