mprobus mprobus - 6 months ago 46
SQL Question

Last Month Data Formula

I have a custom table that has the below information in MS SQL 2014. I am trying to come up with a way to calculate the following formula.

ABS(LastMonthTotal + LastMonthNew – ThisMonthTotal) = Decoms

i.e. ABS(AugustTotal + AugustNew – SeptemberTotal). This should yield 557.

I was thinking of using case statements inside of the ABS, but can't get it to work properly.

creationmonth MonthCreated TotalCount NewCount RetreivedON

8 August 11238 1629 8/1/2016 0:00

9 September 12310 721 9/1/2016 0:00

This is what I came up with so far, but it doesn't work. I was hoping after I got this part working, I could get the dynamic part working to look for this month and last month.

abs((case when CreationMonth = '8' then totalcount end) + (case when CreationMonth = '8' then newcount end) - (case when creationmonth = '9' then totalcount end))


Perfect opportunity to use a window function:

 LAG(TotalCount) OVER (ORDER BY CreationMonth) AS LastMonthTotal 
,LAG(NewCount) OVER (ORDER BY CreationMonth) AS LastMonthNew 
,TotalCount AS ThisMonthTotal
,ABS(LAG(TotalCount) OVER (ORDER BY CreationMonth) 
   + LAG (NewCount) OVER (ORDER BY CreationMonth) 
   - TotalCount) AS Decoms
from TableX;

The LAG function will give you the value form the previous row. 'Previous row' is defined by the order in the OVER clause. In this case you just order by the creationmonth.