mprobus - 1 year ago 70

SQL Question

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

Answer Source

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.