CODEWITHSUNDEEP
sqlsql-serversql-server-2014
mprobus
mprobus

Reputation: 15

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

Upvotes: 0

Views: 56

Answers (2)

MattPerry
MattPerry

Reputation: 306

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.

Upvotes: 1

Kostya
Kostya

Reputation: 1605

something like

ABS(LastMonthTotal + LastMonthNew – LEAD(ThisMonthTotal, 1,0)OVER (ORDER BY  RetreivedON asc)  )

Upvotes: 0

Related Questions