Waller Waller - 1 month ago 9
SQL Question

Calculating based on the above row SQL

I am trying to work out what the previous months invoice value was, and put it on the same row as the current value.

I'm using SQL Server.Microsoft SQL Server 2008 R2 (SP2)

I've tried my luck using CTE's and all kind of weird joins, but my output is never correct. Can someone point me in the correct direction?

Example table:

invoice_month value
--------------------------
201510 337265.386
201511 335466.456
201512 338646.500
201601 333440.380
201602 330731.208
201603 339299.752
201604 340878.168
201605 359585.382
201606 339616.430
201607 316457.486
201608 308009.976
201609 232196.268
201610 894839.180
201611 232196.268
201612 232196.268
201701 232196.268
201702 232196.268
201703 232196.268


Expected output would be:

invoice_month value, prevValue
--------------------------------------------
201510 337265.386, NULL
201511 335466.456, 337265.386
201512 338646.500, 335466.456
201601 333440.380, 333440.380
201602 330731.208, 333440.380
201603 339299.752, 330731.208
201604 340878.168, 339299.752
201605 359585.382, 340878.168
201606 339616.430, 359585.382
201607 316457.486, 339616.430
201608 308009.976, 316457.486
201609 232196.268, 308009.976
201610 894839.180, 232196.268
201611 232196.268, 894839.180
201612 232196.268, 232196.268
201701 232196.268, 232196.268
201702 232196.268, 232196.268
201703 232196.268, 232196.268

Answer

for versions prior to 2012:

;with cte
 as
(select invoice_month,value,
row_number() over (order by invoice_month) as rn
from  #temp  )
select c1.*,c2.value
from cte c1
left join
cte c2
on c1.rn=c2.rn+1
Comments