How could I calculate Quarter and Year to date (current month)

user_8462809 0
Viewed 133

I have a query to calculate current period revenue for each zone and total like below:

with TOTAL as (
    select
        "Period",
        "Zone",
        "Country",
        "Tag",
        "Name",
        "Program",
        "Revenue",
        DATE_TRUNC('QUARTER', "Period") "Quarter",
        DATE_TRUNC('YEAR', "Period")    "Year"
    from "Database"."Schema"."Revenue"
)
select
    total.*,
    IFF(UPPER("Zone") = 'EMEA', "Revenue", 0) as "Revenue EMEA",
    IFF(UPPER("Zone") = 'APAC', "Revenue", 0) as "Revenue APAC",
    IFF(UPPER("Zone") = 'NA', "Revenue", 0) as "Revenue NA",
    DATEADD(MONTH, -1, "Period") as "Period M1",
    LAG("Revenue", 1, 0) OVER (PARTITION BY "Zone","Country","Tag", "Name", "Program" ORDER BY "Period") as "Revenue M1",
    LAG("Revenue EMEA", 1, 0) OVER (PARTITION BY "Zone","Country","Tag", "Name", "Program" ORDER BY "Period") as "Revenue EMEA M1",
    LAG("Revenue APAC", 1, 0) OVER (PARTITION BY "Zone","Country","Tag", "Name", "Program" ORDER BY "Period") as "Revenue APAC M1",
    LAG("Revenue NA", 1, 0) OVER (PARTITION BY "Zone","Country","Tag", "Name", "Program" ORDER BY "Period") as "Revenue NA M1",
    DATEADD(MONTH, -2, "Period") as "Period M2",
    LAG("Revenue", 2, 0) OVER (PARTITION BY "Zone","Country","Tag", "Name", "Program" ORDER BY "Period") as "Revenue M2",
    LAG("Revenue EMEA", 2, 0) OVER (PARTITION BY "Zone","Country","Tag", "Name", "Program" ORDER BY "Period") as "Revenue EMEA M2",
    LAG("Revenue APAC", 2, 0) OVER (PARTITION BY "Zone","Country","Tag", "Name", "Program" ORDER BY "Period") as "Revenue APAC M2",
    LAG("Revenue NA", 2, 0) OVER (PARTITION BY "Zone","Country","Tag", "Name", "Program" ORDER BY "Period") as "Revenue NA M2"
from total

How could I efficiently calculate the same values (Revenue,EMEA,APAC,NA) for:

  • quarter to date (period) : values between "Quarter" which is the begin of actual quarter period and "Period" which is the actual report period)
  • Year to date (Period): values between "Year" which is the begin of actual year period and "Period" which is the actual report period)
  • last full quarter (full quarter period: 3 month period from last quarter)
  • last full year (full year period: all month during last year)

EDIT:

I have partitionned by quarter. Now i need it by quarter to date how can i do this ?

SUM("Revenue") OVER (PARTITION BY "Zone","Country","Tag","Quarter","Year" "Name", "Program" ORDER BY "Period") as "Revenue Q",
SUM("Revenue EMEA") OVER (PARTITION BY "Zone","Country","Tag","Quarter","Year", "Name", "Program" ORDER BY "Period") as "Revenue EMEA Q",
SUM("Revenue APAC") OVER (PARTITION BY "Zone","Country","Tag","Quarter","Year", "Name", "Program" ORDER BY "Period") as "Revenue APAC Q",
SUM("Revenue NA") OVER (PARTITION BY "Zone","Country","Tag","Quarter","Year", "Name", "Program" ORDER BY "Period") as "Revenue NA Q"
windowing snowflake-cloud-data-platform time sql
1 Answers

Sounds like a bit of a "homework" question but I imagine to do the last year one that you would add something like this. You should be able to figure out the rest based on this pattern. It's hard to answer without some sample data:

DATEADD(YEAR, -1, "Year") as "Year 1 ago",
LAG("Revenue", 1, 0) OVER (PARTITION BY "Zone","Country","Tag", "Name", "Program" ORDER BY "Year") as "Revenue Y1 ago",
LAG("Revenue EMEA", 1, 0) OVER (PARTITION BY "Zone","Country","Tag", "Name", "Program" ORDER BY "Year") as "Revenue EMEA Y1 ago",
LAG("Revenue APAC", 1, 0) OVER (PARTITION BY "Zone","Country","Tag", "Name", "Program" ORDER BY "Year") as "Revenue APAC Y1 ago",
LAG("Revenue NA", 1, 0) OVER (PARTITION BY "Zone","Country","Tag", "Name", "Program" ORDER BY "Year") as "Revenue NA Y1 ago"
user_32872500
Related