Sql-server – Converting Monthly Rolling SUM (YTD) back to Monthly numbers

sql serversql-server-2008

I'm having a problem converting rolling SUM by month (monthly year to date) back to only that month's numbers.

For example, see table below. Periods are (YYMM) and a monthly period per year starts with 07 and end with 06:

Name         Period         Amount
AAA          1611             10
BBB          1611             15
CCC          1611             20
AAA          1612             12
BBB          1612             18
CCC          1612             24
AAA          1701             13
BBB          1701             20
CCC          1701             27

The result that we are after is as follows. Period 1611 is the lowest in this example, but can be any YYMM month (07,08,09,10,11,12,01,02,03,04,05,06):

Name         Period         Amount
AAA          1611             10 
BBB          1611             15
CCC          1611             20
AAA          1612             2
BBB          1612             3
CCC          1612             4
AAA          1701             1
BBB          1701             2
CCC          1701             3

Basically, it needs to take a higher period's value, minus the next period below it according to the Group By. If it can't find a lower period, then it keeps data the same.

Currently what I did was to Group By the data from the table I'm pulling it from as there would be multiple lines for each combination and we only need unique ones according to the group by.

Select
            
a.[Actuality], a.[Period], a.[(C) Company Code], a.[(C) Account Code], a.[(C) D1 Code],
a.[(C) D2 Code], a.[(C) D3 Code], a.[(C) D4 Code], a.[(C) Intercompany (To)], 
a.[(C) Intercompany (From)], a.[(C) Type], Sum(a.[Amount]) as 'Amount'
            
From Table1 as a
            
Group By
a.[Actuality], a.[Period], a.[(C) Company Code], 
a.[(C) Account Code], a.[(C) D1 Code], a.[(C) D2 Code]

I was thinking about putting a Where with a difference between periods needing to be 1 (1608-1607) or 89 (1701-1612) or 0 with AND a . [Period] needing to be smallest value for that Group by.

Could I please ask for help in formulating this in SQL?

Additional information

  • In the table only 1 year data is stored. Next year the data is moving to another table and its starts all over again from Period YY07 to YY06
  • In a table during the year around 2.5M rows are made
  • There are no duplicates in the table as a Group by is used to eliminate them
  • Variance between two lines should be also shown if a line exist 1 month and the next month doesn't: YTD in Period 1611 ABC has 20 and if Period 1612 ABC has 0 the line wouldn't appear in the YTD table, but in the month to date (MTD) would need to show Period 1612 ABC -20 (0-20)

Best Answer

select name,period
    ,amount-lag(amount,1,0) over (partition by name order by period)
from table1

with cte as 
( 
     select *
           ,row_number() over (partition by name order by period) as rn
      from table1
 )
 select t1.name,t1.period
       ,t1.amount-coalesce(t2.amount,0)
 from   cte as t1
        left join cte as t2
        on t2.rn = t1.rn-1
Related Question