Cumulative Sum on full calendar

Question

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

Answer 1

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

Answer 2

Hi @Mehdi Djemame ,

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.

Answer 3

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

Answer 4

Hi @Mehdi Djemame ,

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.