CODEWITHSUNDEEP
timesnowflake-cloud-data-platform
Jonito
Jonito

Reputation: 459

how to retrieve quarter and year values to date

I am using the table below:

Period County Cost product
01/11/2019 DE 1.54 Product1
01/11/2019 US 2.35 Product1
01/11/2019 DE 4.21 Product2
01/11/2019 US 0.87 Product2
01/12/2019 DE 1.456 Product1
01/01/2020 DE 5.3 Product2
01/02/2020 US 9.76 Product1
01/03/2020 DE 6.78 Product1
01/03/2020 US 1.2 Product2
01/03/2020 DE 3.964 Product2
01/04/2020 US 2.58 Product1
01/04/2020 US 2.8 Product2
01/04/2020 DE 0.5 Product1

Code to test:

    CREATE OR REPLACE TEMPORARY TABLE "TMP_TEST" (
        "Period"  DATE,
        "Country"     VARCHAR,
        "Cost"    FLOAT,
        "Product"    VARCHAR
    );
    
    INSERT INTO "TMP_TEST" 
    VALUES 
('01/11/2019','DE','1.54','Product1'),
('01/11/2019','US','2.35','Product1'),
('01/11/2019','DE','4.21','Product2'),
('01/11/2019','US','0.87','Product2'),
('01/12/2019','DE','1.456','Product1'),
('01/01/2020','DE','5.3','Product2'),
('01/02/2020','US','9.76','Product1'),
('01/03/2020','DE','6.78','Product1'),
('01/03/2020','US','1.2','Product2'),
('01/03/2020','DE','3.964 ','Product2'),
('01/04/2020','US','2.58','Product1'),
('01/04/2020','US','2.8','Product2'),
('01/04/2020','DE','0.5 ','Product1');

select * from TMP_TEST;

WITH TOTAL AS (
    SELECT
        "Period","Country","Cost","Product"
    FROM "TMP_TEST"
)
SELECT
    TOTAL.*,
    IFF(UPPER("Country") = 'DE', "Cost", 0) as "Cost DE",
    IFF(UPPER("Country") = 'US', "Cost", 0) as "Cost US",
    DATEADD(MONTH, -1, "Period") AS "Period M-1",
    LAG("Cost", 1, 0) OVER (PARTITION BY "Country", "Product" ORDER BY "Period") AS "Cost USD M1",
    LAG("Cost DE", 1, 0) OVER (PARTITION BY "Country", "Product" ORDER BY "Period") AS "Cost DE M1",
    LAG("Cost US", 1, 0) OVER (PARTITION BY "Country", "Product" ORDER BY "Period") AS "Cost US M1"
FROM TOTAL

Now i want the same results adding a column for the total cost by product for current quarter (imagine we are on period 01/02/2020, i want cumulative sum for the product Quarter to date (january & febuary included))

Upvotes: 1

Views: 45

Answers (1)

akshindesnowflake
akshindesnowflake

Reputation: 601

WITH TOTAL AS (
    SELECT
        "Period","Country","Cost","Product"
    FROM "TMP_TEST"
)
SELECT
    DATE_TRUNC('Q',"Period") AS "Quarter",  -- New Quarter Column added for Quarter
    TOTAL.*,
    IFF(UPPER("Country") = 'DE', "Cost", 0) as "Cost DE",
   IFF(UPPER("Country") = 'US', "Cost", 0) as "Cost US",
    DATEADD(MONTH, -1, "Period") AS "Period M-1",
   LAG("Cost", 1, 0) OVER (PARTITION BY "Country", "Product" ORDER BY "Period") AS "Cost USD M1",
   LAG("Cost DE", 1, 0) OVER (PARTITION BY "Country", "Product" ORDER BY "Period") AS "Cost DE M1",
   LAG("Cost US", 1, 0) OVER (PARTITION BY "Country", "Product" ORDER BY "Period") AS "Cost US M1",
   SUM("Cost") OVER (PARTITION BY "Country", "Product","Quarter" ORDER BY "Period") AS"Quarter Cost", -- New Column added For Aggregation By Quarter TO Date

   SUM("Cost DE") OVER (PARTITION BY "Country", "Product","Quarter" ORDER BY "Period") AS"Quarter Aggregation Cost DE" -- New Column added For Aggregation By Quarter TO Date
 
FROM TOTAL;

Upvotes: 1

Related Questions