3

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)
Improve this question
0

1 Answer 1

Reset to default
1 
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
Improve this answer
5
  • This seems to be intended for use with running totals but not with rolling totals. If the source has more than one year's worth of data, your code may not work, because the totals are YTD (year-to-date), not the running totals that include all the months since the beginning. (So at some point you can no longer just subtract the previous total from the current total to get the current month, you need to account for "losing" the month that was a year ago.)
    – Andriy M
    Jan 16, 2017 at 10:26
  • Thanks guys! Regarding running totals that shouldnt be a problem, because every year all years data is moved to a separate table and in that one only that years is kept. However the problem with this code im having is that it overload the _log file and my server runs out of memory before it finishes it (gets to extra 20GB). It is running on a table with 1.7M rows, so its expected to have some usage, but this is a bit too much. Would there be a way to adjust so that all the transactions arent posted to _log and to just create a table of this query?
    – Lukas
    Jan 16, 2017 at 12:56
  • @Lukas It looks like you need to join on more than just the rn column. I suspect that you also want to join on the name column to prevent duplicate rows from showing up. It sounds like your query is generating more data than it should and that's why you run out of t-log space and use a lot of memory. You can also try running the query as a SELECT COUNT(*) to verify that you're getting back the right number of rows.
    – Joe Obbish
    Jan 17, 2017 at 0:29
  • Hi Joe, that might be it. It has more rows, but i thought its because its showing variance between two lines out of which 1 was not existing before (i.e. if ABC period 1611 has 20 and theres nothing in period 1612, then a new line created of -20 for MTD). I tried to add this t2.rn = t1.rn-1 and t2.name = t2.name -1 but its giving me convertion errors from nvarchar to int, i assume i need to put something in the cte as well, just not sure what?
    – Lukas
    Jan 17, 2017 at 5:40
  • Ah because i was subtracting an int from a nvarchar >_> But in the beginning we are pulling Select * so doesnt left join cte as t2 has filter for name as well? Though another thought is that before going through this in the table i ran a Group By, so i dont think there should be duplicates
    – Lukas
    Jan 17, 2017 at 6:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.