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
creationmonth MonthCreated TotalCount NewCount RetreivedON
8 August 11238 1629 8/1/2016 0:00
9 September 12310 721 9/1/2016 0:00
Perfect opportunity to use a window function:
SELECT 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.