question

MehdiDjemame-4194 avatar image
0 Votes"
MehdiDjemame-4194 asked MelissaMa-msft commented

Cumulative Sum on full calendar

Hi all,

I have a table F_SALES

Region SalesRep Month Sales
North John 2021-01 10
North Jack 2021-01 12
North John 2021-02 15
North Jack 2021-03 10
North Jones 2021-03 10
North John 2021-03 10
South Jack 2021-01 20
South Jack 2021-03 25
....


I need to calculate a cumulative Sum over the months.

I tried using the PARTITION BY clause, as folllows:

Select Region,
Month,
` Sum(Sales) over (Partition by Region Month Order by Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
as Sales_MTD
From F_SALES


The result is:

Region Month Sales
North 2021-01 22
North 2021-02 15
North 2021-03 30
South 2021-01 20
South 2021-03 25

My problem is: There is obviously no cumulated value for South 2021-02, because so is no record in the original table.

Any idea about how to "fill" the gaps in the cumulative sum ?
Or any workaround to suggest ?

TIA !

Mehdi










sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm not seeing a cumulative sum there but rather aggregated by region and month

Have you got a date table you can lean in to? that way you can use the date table as the driver (selecting one day per month) and left outer join your aggregation table (even better if your fact actually has a date column)

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

If no simpler query, then try this one:

 ;
 with L as
 (
     select Region, min(Month) mi, max(Month) ma
     from F_SALES
     group by Region
 ),
 D as
 (
     select Region, Month, sum(Sales) as Sales
     from F_SALES
     group by Region, Month
 )
 select L.Region, g as Month, isnull(Sales, 0) as Sales,
     sum(Sales) over (partition by L.Region order by g rows between unbounded preceding and current row) as CumulativeSales
 from L
 cross apply (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12) ) t(m)
 cross apply (values (concat(substring(mi, 1, 4), format(m, '-00')))) z(g)
 left join D on D.Region = L.Region and D.Month = g
 where g >= mi and g <= ma
 order by Region, Month

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @MehdiDjemame-4194,

Welcome to Microsoft Q&A!

Please also refer below:

 ;with cte as (
 select * from 
 (select distinct Region from F_SALES) a
 cross apply (select distinct [Month] from F_SALES) b)
 ,cte1 as (
 Select a.Region,
 a.[Month],
 isnull(Sum(Sales) over (Partition by a.Region, a.[Month] Order by a.[Month] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),0)
 as Sales_MTD
 From cte a 
 left join F_SALES b on a.Region=b.Region and a.[Month]=b.[Month])
 select a.Region,a.[Month],Sales_MTD Sales_MTD
  from cte1 a
 inner join (select Region,[Month],max(Sales_MTD) max from cte1 group by Region,[Month]) b
 on a.Region=b.Region and a.Month=b.Month and a.Sales_MTD=b.max

OR

 ;with cte as (
 select Region, min(Month) min, max(Month) max from F_SALES group by Region)
 ,cte1 as (
 SELECT Region,CONVERT(date, min+'-01') dates,CONVERT(date,max+'-01') AS max from cte
 UNION ALL
 SELECT Region,DATEADD(MONTH, 1, dates),max
 FROM cte1
 WHERE CONVERT(date, dates) < CONVERT(date, max))
 ,cte2 as (
 Select a.Region,
 convert(varchar(7),a.dates) [Month],
 isnull(Sum(Sales) over (Partition by a.Region, convert(varchar(7),a.dates) Order by convert(varchar(7),a.dates) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),0)
 as Sales_MTD
 From cte1 a 
 left join F_SALES b on a.Region=b.Region and convert(varchar(7),a.dates)=b.[Month])
 select a.Region,a.[Month],Sales_MTD Sales_MTD
 from cte2 a
 inner join (select Region,[Month],max(Sales_MTD) max from cte2 group by Region,[Month]) b
 on a.Region=b.Region and a.Month=b.Month and a.Sales_MTD=b.max

Output:

 Region Month Sales_MTD
 North 2021-01 22
 North 2021-02 15
 North 2021-03 30
 South 2021-01 20
 South 2021-02 0
 South 2021-03 25

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MehdiDjemame-4194 avatar image
0 Votes"
MehdiDjemame-4194 answered

Hi Melissa,

Thanks for your help.

It works perfectly, and the outcome is the expected one.

However, I'm just wondering how this will evolve in terms of performances.
IActually the query takes already 2 minutes to execute from my side.
And in fact, I have more columns than the simple "Region" (it's more like Region, Product, Sales Rep...).
So, the number of lines will increase over the months, and consequently the execution time.

I was wondering if there is a better option in terms of performances.
Maybe an exécution in 2 steps:
Query A to calculate and store the Previous years YTD (yyyy_M12_YTD basically)
Query B to calculate and store the MTD on the current year + Append the yyyy_M12_YTD

Any experience with this please ?

Thanks

Mehdi

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft commented

Hi @MehdiDjemame-4194,

Thanks for your update.

It is recommended for you to post more sample data and expected output if you have extra requirement.

I added some sample data as below:

 insert into  F_SALES values
 ('North','John','2020-12',30),
 ('North','Jack','2020-12',20),
 ('North','Jones','2019-11',10),
 ('North','John','2019-10',30),
 ('South','Jack','2020-09',20),
 ('South','Jones','2020-09',11),
 ('South','Jones','2019-08',12)

Please check whether below whether it is helpful to you.

Query A to calculate and store the Previous years YTD (yyyy_M12_YTD basically)

 ;with cte as (
 select Region, year(cast(Month+'-01' as date)) [year],sales from F_SALES 
 where YEAR(cast(Month+'-01' as date))<YEAR(GETDATE()))
 select Region,year,sum(sales) Sales_MTD
 from cte
 group by Region,year

Output:

 Region year Sales_MTD
 North 2019 40
 South 2019 12
 North 2020 50
 South 2020 31

Query B to calculate and store the MTD on the current year + Append the yyyy_M12_YTD

  ;with cte as (
  select Region, min(Month) min, max(Month) max from F_SALES 
  where YEAR(cast(Month+'-01' as date))=YEAR(GETDATE()) 
  group by Region)
  ,cte1 as (
  SELECT Region,CONVERT(date, min+'-01') dates,CONVERT(date,max+'-01') AS max from cte
  UNION ALL
  SELECT Region,DATEADD(MONTH, 1, dates),max
  FROM cte1
  WHERE CONVERT(date, dates) < CONVERT(date, max))
  Select a.Region,
  convert(varchar(7),a.dates) [Month],
  Sum(isnull(Sales,0)) as Sales_MTD
  From cte1 a 
  left join F_SALES b on a.Region=b.Region and convert(varchar(7),a.dates)=b.[Month]
  group by a.Region, convert(varchar(7),a.dates)
  order by a.Region,convert(varchar(7),a.dates)

Output:

 Region Month Sales_MTD
 North 2021-01 22
 North 2021-02 15
 North 2021-03 30
 South 2021-01 20
 South 2021-02 0
 South 2021-03 25

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MehdiDjemame-4194,

Could you please validate above and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·