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?
- 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)