I am trying to create a snowflake procedure to unpivot columns to rows. During unpivoting, I need to populate year and month for each column to row. The parameter for the procedure will be current month and year.
I need to do unpivoting and populate each row with sliding window year_num and month_num. i.e. for each unpivot, the column 'num' should start to populate with 'num_1' from source table along with the (currentdate - 5 years) and populate the corresponding year_num and month_num as separate columns.
So as per the current date (2nd Aug 2021), num_1 should be unpivoted and populated with 08 as month_num and 2016 as year_num. Similarly column num_2 of the same source row after unpivot should have 09 as month_num and 2016 as year_num in the target as a separate row.
This should go on till 12 years from 2016 till 2028 for columns till num_146.
Example:
The source table in snowflake has columns called num from num_1 till num_146
I do unpivot using insert into target_table id, name, num, year, month Select from source table unpivot(num for columnname in (num_1, num_2, num_3 ..................... till num_146);
But, I could not figure out how to implement the sliding window concept of current date -5 years to pull the month, year and increment them for next 12 years until we reach the end of each row, and do the same for all unpivoting.
Could anyone please help me how to achieve this in Snowflake procedure which is based on javascript?
Thank you.
Here is how I would solve it in SQL, I suppose you can incorporate the logic into your procedure.
When you unpivot, add a column that represents the row number. This will help you to calculate the offset month and year later. You can use the row_number() function to get it
row_number() over(partition by null order by null) as RN
Then use this column to calculate the offset month and year (assuming that current_date is the starting point in time)
year(add_months(current_date, RN - 61)) as year,
month(add_months(current_date, RN - 61)) as month
Subtracting 61 months from the first row will give you -60 which is the month and year from 5 years ago, resulting in August 2016. If the last row is 146, then subtracting 61 will give you 85 which is 85 months into the future, resulting in September 2028.
Finally, if the input parameters are month and year (named p_month and p_year respectively), you can substitute current_date with the date_from_parts function
year(add_months(date_from_parts(p_year, p_month, 01 ), RN - 61)) as year,
month(add_months(date_from_parts(p_year, p_month, 01 ), RN - 61)) as month