Search code examples
snowflake-cloud-data-platformsnowflake-schema

Split quarters to individual months

I have a table like below. The period column is of varchar type and indicates the year and quarter.

PERIOD YEAR USD
2021/03 2021 100
2021/06 2021 100

How can I split the period columns to months that make up the quarter and get the following output?

PERIOD YEAR USD
2021/01 2021 33.33
2021/02 2021 33.33
2021/03 2021 33.33
2021/04 2021 33.33
2021/05 2021 33.33
2021/06 2021 33.33
Solution

  • I'll tackle the date manipulation and leave the rest to you. I prefer using date functions instead of string manipulations in case I later decide to do something else with the dates

    set period='2020/03';

select $period, to_char(add_months((replace($period,'/','-')||-'01')::date,-0),'yyyy/mm')
union all
select $period, to_char(add_months((replace($period,'/','-')||-'01')::date,-1),'yyyy/mm')
union all
select $period, to_char(add_months((replace($period,'/','-')||-'01')::date,-2),'yyyy/mm')