none
T-sql Query help to calculate Monthly YTD value RRS feed

  • Question

  • Hi Team,

    I have following table structure.

    Basically ,I want to calculate Monthly YTD value based on Company

    Table : Sales

    Company  Month  Year     Value
    A              1     2019          100
    A              1     2019         500
    A              2     2019         200
    A              2     2019          600

    B              1     2019         700
    B              1     2019         500
    B              2     2019         400
    B              2     2019          600


    Output 

    Company  Month  Year     Value
    A          1     2019           600
    A          2     2019           1400
    B          1     2019           1200
    B          2     2019           2200

    • Edited by Aminesh Tuesday, January 21, 2020 10:51 AM
    Tuesday, January 21, 2020 8:49 AM

Answers

  • Check this query:

    select distinct 
       Company, [Month], [Year],
       sum([Value]) over (partition by Company, [Year] order by [Month] 
             range between unbounded preceding and current row ) as [Value]
    from MyTable
    order by Company, [Year], [Month]

    and this:

    ;
    with Q as
    (
       select Company, [Month], [Year],
             sum([Value]) as [Value]
       from MyTable
       group by Company, [Year], [Month]
    )
    select Company, [Month], [Year],
       sum([Value]) over (partition by Company, [Year] order by [Month] 
             range between unbounded preceding and current row  ) as [Value]
    from Q
    order by Company, [Year], [Month]
    





    • Edited by Viorel_MVP Tuesday, January 21, 2020 11:06 AM
    • Marked as answer by Aminesh Wednesday, January 22, 2020 5:05 AM
    Tuesday, January 21, 2020 10:57 AM

All replies

  • Check this query:

    select distinct 
       Company, [Month], [Year],
       sum([Value]) over (partition by Company, [Year] order by [Month] 
             range between unbounded preceding and current row ) as [Value]
    from MyTable
    order by Company, [Year], [Month]

    and this:

    ;
    with Q as
    (
       select Company, [Month], [Year],
             sum([Value]) as [Value]
       from MyTable
       group by Company, [Year], [Month]
    )
    select Company, [Month], [Year],
       sum([Value]) over (partition by Company, [Year] order by [Month] 
             range between unbounded preceding and current row  ) as [Value]
    from Q
    order by Company, [Year], [Month]
    





    • Edited by Viorel_MVP Tuesday, January 21, 2020 11:06 AM
    • Marked as answer by Aminesh Wednesday, January 22, 2020 5:05 AM
    Tuesday, January 21, 2020 10:57 AM
  • Hi Viorel,

    Below query is working fine

    select distinct 
       Company, [Month], [Year],
       sum([Value]) over (partition by Company, [Year] order by [Month] 
             range between unbounded preceding and current row ) as [Value]
    from MyTable
    order by Company, [Year], [Month]

    Is there any specific reason , I need to use other query ?

    Tuesday, January 21, 2020 12:19 PM
  • [...] Is there any specific reason , I need to use other query ?

    Maybe the experimental average performance of methods is different in case of real large data.


    Tuesday, January 21, 2020 1:16 PM

  • Is there any specific reason , I need to use other query ?

    Hi Aminesh, 

    We just provide some different ways to get your expected result . And you can choose to use freely . Hope it will help you.

    IF OBJECT_ID('Sales') IS NOT NULL drop table  Sales   
    go 
    create table Sales
    (Company varchar(10),
    Month int, 
    Year int,
    Value int)
    insert into Sales values 
    ('A',1,2019,100),
    ('A',1,2019,500),
    ('A',2,2019,200),
    ('A',2,2019,600),
    ('B',1,2019,700),
    ('B',1,2019,500),
    ('B',2,2019,400),
    ('B',2,2019,600)
    ;with cte as (
    select Company,Month,Year,sum(Value)Value  
    from Sales group by Company,Month,Year
    )
    select Company,Month,Year,
    sum(Value)over(partition by Company,Year order by Month )  Value
    from cte
    /*Company    Month       Year        Value
    ---------- ----------- ----------- -----------
    A          1           2019        600
    A          2           2019        1400
    B          1           2019        1200
    B          2           2019        2200
    */

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 22, 2020 3:33 AM