CODEWITHSUNDEEP
sqlt-sqlsnowflake-cloud-data-platform
imjusttryingtolearnstuff
imjusttryingtolearnstuff

Reputation: 29

SQL - YTD and MTD in rows instead of columns

Let's say I have this table:

FiscalPeriod FiscalMonth FiscalYear Division L4 L5 L6 L7 AMT_LEGAL_USD
01-2022 1 2022 A NOP OP GM NS 1000
02-2022 2 2022 A NOP OP GM NS 2000
03-2022 3 2022 A NOP OP GM NS 2000
01-2022 1 2022 B NOP OP GM NS 1000
02-2022 2 2022 B NOP OP GM NS 3000
03-2022 3 2022 B NOP OP GM NS 5000
01-2022 1 2022 C NOP OP GM NS 1000
02-2022 2 2022 C NOP OP GM NS 1000
03-2022 3 2022 C NOP OP GM NS 1000

I'm trying to build a table that queries this one and calculates MTD (month to date) and YTD (Year to Date): for each Division, caculate the MTD and YTD based on FiscalMonth and FiscalYear columns, for each of my KPIs (columns L4 to L7)

I have the following query:

SELECT
    ec.FiscalPeriod, 
    ec.FiscalMonth, 
    ec.FiscalYear, 
    ec.Divison,
    sum(CASE WHEN L7 = 'NS' then EC.AMT_LEGAL_USD else 0 end) as NS_MTD,
    sum(NS_MTD) OVER (PARTITION BY ec.FiscalYear, ec.Divison ORDER BY ec.FiscalPeriod) AS NS_YTD,
    sum(CASE WHEN L6 = 'GM' then EC.AMT_LEGAL_USD else 0 end) as GM_MTD,
    sum(GM_MTD) OVER (PARTITION BY ec.FiscalYear, ec.Divison ORDER BY ec.FiscalPeriod) AS GM_YTD,
    sum(CASE WHEN L5 = 'OP' then EC.AMT_LEGAL_USD else 0 end) as OP_MTD,
    sum(OP_MTD) OVER (PARTITION BY ec.FiscalYear, ec.Divison ORDER BY ec.FiscalPeriod) AS OP_YTD,
    sum(CASE WHEN L4 = 'NOP' then EC.AMT_LEGAL_USD else 0 end) as NOP_MTD,
    sum(NOP_MTD) OVER (PARTITION BY ec.FiscalYear, ec.Divison ORDER BY ec.FiscalPeriod) AS NOP_YTD,
from my table ec
    group by 1,2,3,4
    having Net_Sales_MTD != 0 and GM_MTD != 0 and OP_MTD != 0 and NOP_MTD != 0
    order by 1 asc;

That will return me something like:

FiscalPeriod FiscalMonth FiscalYear Division NS_MTD NS_YTD GM_MTD GM_YTD OP_MTD OP_YTD NOP_MTD NOP_YTD
01-2022 1 2022 A 1000 1000 1000 1000 1000 1000 1000 1000
02-2022 2 2022 A 2000 3000 2000 3000 2000 3000 2000 3000
03-2022 3 2022 A 2000 5000 2000 5000 2000 5000 2000 5000
01-2022 1 2022 B 1000 1000 1000 1000 1000 1000 1000 1000
02-2022 2 2022 B 3000 4000 3000 4000 3000 4000 3000 4000
03-2022 3 2022 B 5000 9000 5000 9000 5000 9000 5000 9000
01-2022 1 2022 C 1000 1000 1000 2000 1000 3000 1000 1000
02-2022 2 2022 C 1000 2000 1000 2000 1000 2000 1000 2000
03-2022 3 2022 C 1000 3000 1000 3000 1000 3000 1000 3000

I would like to get an output that would look like:

FiscalPeriod FiscalMonth FiscalYear Division Period NOP OP GM NS
01-2022 1 2022 A MTD 1000 1000 1000 1000
01-2022 1 2022 A YTD 1000 1000 1000 1000
02-2022 2 2022 A MTD 2000 2000 2000 2000
02-2022 2 2022 A YTD 3000 3000 3000 3000
03-2022 3 2022 A MTD 2000 2000 2000 2000
03-2022 3 2022 A YTD 5000 5000 5000 5000
01-2022 1 2022 B MTD 1000 1000 1000 1000
01-2022 1 2022 B YTD 1000 1000 1000 1000
02-2022 2 2022 B MTD 3000 3000 3000 3000
02-2022 2 2022 B YTD 4000 4000 4000 4000
03-2022 3 2022 B MTD 5000 5000 5000 5000
03-2022 3 2022 B YTD 9000 9000 9000 9000
01-2022 1 2022 C MTD 1000 1000 1000 1000
01-2022 1 2022 C YTD 1000 1000 1000 1000
02-2022 2 2022 C MTD 1000 1000 1000 1000
02-2022 2 2022 C YTD 2000 2000 2000 2000
03-2022 3 2022 C MTD 1000 1000 1000 1000
03-2022 3 2022 C YTD 3000 3000 3000 3000

Any ideas on how this can be achivied? Hopefully that wasn't too confusing.

Appreciate any feedback and ideas on how to do this.

Upvotes: 0

Views: 191

Answers (1)

Rodrigo Cava
Rodrigo Cava

Reputation: 173

I think you'll need to do this in two steps:

  1. Calculate MTD
  2. Calculate YTD
  3. Union All

Here is an example based on your data.

with mtd as (
    SELECT
        ec.FiscalPeriod, 
        ec.FiscalMonth, 
        ec.FiscalYear, 
        ec.Division,
        'MTD' as Period,
        sum(CASE WHEN L7 = 'NS' then EC.AMT_LEGAL_USD else 0 end) as NS_MTD,
        sum(CASE WHEN L6 = 'GM' then EC.AMT_LEGAL_USD else 0 end) as GM_MTD,
        sum(CASE WHEN L5 = 'OP' then EC.AMT_LEGAL_USD else 0 end) as OP_MTD,
        sum(CASE WHEN L4 = 'NOP' then EC.AMT_LEGAL_USD else 0 end) as NOP_MTD
    from data ec
    group by 1,2,3,4    
)
, ytd as (
    SELECT
        ec.FiscalPeriod, 
        ec.FiscalMonth, 
        ec.FiscalYear, 
        ec.Division,
        'YTD' as Period,
        sum(NS_MTD) OVER (PARTITION BY ec.FiscalYear, ec.Division ORDER BY ec.FiscalPeriod) AS NS_YTD,
        sum(GM_MTD) OVER (PARTITION BY ec.FiscalYear, ec.Division ORDER BY ec.FiscalPeriod) AS GM_YTD,
        sum(OP_MTD) OVER (PARTITION BY ec.FiscalYear, ec.Division ORDER BY ec.FiscalPeriod) AS OP_YTD,
        sum(NOP_MTD) OVER (PARTITION BY ec.FiscalYear, ec.Division ORDER BY ec.FiscalPeriod) AS NOP_YTD
    from mtd ec
    group by 1,2,3,4, NS_MTD, gm_mtd, op_mtd, op_mtd, nop_mtd
)
   
select * from (
   select * from mtd 
   union all 
   select * from ytd
) foo
order by fiscalyear, division, fiscalperiod, fiscalmonth, period

Upvotes: 1

Related Questions