row_number - over partition sql server ejemplo

Cálculo basado en la fila de SQL anterior

Question

Estoy intentando calcular el valor de la factura de meses anteriores y colocarlo en la misma fila que el valor actual.

Estoy usando SQL Server.Microsoft SQL Server 2008 R2 (SP2)

He intentado con CTE y todo tipo de combinaciones extrañas, pero mi resultado nunca es correcto. ¿Puede alguien señalarme en la dirección correcta?

Tabla de ejemplo:

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

El resultado esperado sería:

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 1

En SQL Server 2012+, usaría lag() :

select t.invoice_month, t.value,
lag(t.value) over (order by t.invoice_month) as prev_value
from t;

Esta es una función estándar ANSI. En versiones anteriores de SQL Server, hay métodos alternativos.

Answer 2

para versiones anteriores a 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

Answer 3

SELECT *
FROM (SELECT TOP 1 invoice_month,
value,
NULL AS [Previous value]
FROM Invoice
ORDER BY invoice_month ASC) T
UNION
SELECT I.invoice_month,
I.value,
I1.value AS [Previous value]
FROM Invoice I
INNER JOIN Invoice I1
ON I.invoice_month - 1 = I1.invoice_month