Snowflake how to obtain cumulative date values

Question

Can someone help me with obtaining the result for below logic. I have a table with below columns.

TYPE    SRC_CURR    TAR_CURR    EX_RATE     EX_RATE_START_DATE  
M       GBP         USD         1.36687     2/1/2021            
M       GBP         USD         1.33636     1/1/2021            
M       GBP         USD         1.32837     12/1/2020           
M       GBP         USD         1.30242     11/1/2020           
M       GBP         USD         1.27421     10/1/2020           
M       GBP         USD         1.31527     9/1/2020            
ZEU     GBP         USD         1.3654      1/20/2021           
ZEU     GBP         USD         1.363       1/19/2021           
ZEU     GBP         USD         1.3587      1/18/2021           
ZEU     GBP         USD         1.359       1/15/2021           
ZEU     GBP         USD         1.3689      1/14/2021           
ZEU     GBP         USD         1.3639      1/13/2021           
ZEU     GBP         USD         1.3664      1/12/2021           
ZEU     GBP         USD         1.3518      1/11/2021           
ZEU     GBP         USD         1.3568      1/8/2021            

So I need to form a new column which is EX_RATE_END_DATE from above values as shown below. Ideally the requirement is to have EX_RATE_END_DATE to max 9999-12-31 by default for the latest start date and for rest of the records it should be previous max start date - 1.

Please find below the output required,

TYPE    SRC_CURR    TAR_CURR    EX_RATE     EX_RATE_START_DATE  EX_RATE_END_DATE
M       GBP         USD         1.36687     2/1/2021            12/31/9999
M       GBP         USD         1.33636     1/1/2021            1/31/2021
M       GBP         USD         1.32837     12/1/2020           12/31/2020
M       GBP         USD         1.30242     11/1/2020           11/30/2020
M       GBP         USD         1.27421     10/1/2020           10/31/2020
M       GBP         USD         1.31527     9/1/2020            9/30/2020
ZEU     GBP         USD         1.3654      1/20/2021           12/31/9999
ZEU     GBP         USD         1.363       1/19/2021           1/19/2021
ZEU     GBP         USD         1.3587      1/18/2021           1/18/2021
ZEU     GBP         USD         1.359       1/15/2021           1/17/2021
ZEU     GBP         USD         1.3689      1/14/2021           1/14/2021
ZEU     GBP         USD         1.3639      1/13/2021           1/13/2021
ZEU     GBP         USD         1.3664      1/12/2021           1/12/2021
ZEU     GBP         USD         1.3518      1/11/2021           1/11/2021
ZEU     GBP         USD         1.3568      1/8/2021            1/10/2021

It would be great if someone help me with getting the desired result set by any possible ways in snowflake.

question from:https://stackoverflow.com/questions/65846668/snowflake-how-to-obtain-cumulative-date-values

Answer 1

Could you please check below query: select TYPE, SRC_CURR, TAR_CURR, EX_RATE, EX_RATE_START_DATE, CASE WHEN DATE_VALUE = 1 THEN '9999-12-31' ELSE LAST_DAY("EX_RATE_START_DATE", 'MONTH') END AS "LAST DAY OF YEAR" from ( SELECT TYPE, SRC_CURR, TAR_CURR, EX_RATE, EX_RATE_START_DATE, row_number() over(partition by type order by EX_RATE_START_DATE desc) as date_value FROM SOF_TEST1) a;