Cumulative Sum on full calendar

Mehdi Djemame 1 Reputation point
2021-07-13T13:34:44.693+00:00

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

Developer technologies | Transact-SQL
{count} votes

4 answers

Sort by: Most helpful
  1. Viorel 122.6K Reputation points
    2021-07-13T14:56:06.573+00:00

    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
    
    0 comments No comments

  2. MelissaMa-MSFT 24,221 Reputation points
    2021-07-14T02:27:54.32+00:00

    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.

    0 comments No comments

  3. Mehdi Djemame 1 Reputation point
    2021-07-14T10:14:15.983+00:00

    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

    0 comments No comments

  4. MelissaMa-MSFT 24,221 Reputation points
    2021-07-15T05:53:35.81+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.