Previous Row Calculations

Hi all

I am wondering if anyone know a way that you can look up a value from the previous row to do a calculation on. I have a field that I need to subtract the same field from the previous row to validate. Is this possible in a query

Thanks
Kenzie

Answer this question

Previous Row Calculations

R. P. Poletti

Ok, i just composed this script and i hope it could help you

WITH MyTable AS
(SELECT
*,
Num = ROW_NUMBER() OVER(ORDER BY <YourColumn>)
FROM [dbo].<YourTable>)

SELECT
tb.<YourColumn>,
tb.Num,
prev. <YourColumn>
FROM MyTable tb
LEFT JOIN
(SELECT
<YourColumn>,
Num = (ROW_NUMBER() OVER(ORDER BY <YourColumn>) -1)
FROM [dbo].<YourTable>) prev
ON tb.Num = prev.Num

Mind to change <YourColumn> and <YourTable>
let me know if it works (for me it's working)

JessieLiu
Slight simplification, you can use only the CTE in the self-join like:

WITH t_seq
AS
(
select ROW_NUMBER() OVER(ORDER BY <column_list>) AS seq
from <table>
)
select ...
from t_seq AS t1
left join t_seq AS t2
on t2.seq = t1.seq -1;
Previous Row Calculations

Answer this question