Calculate difference between previous row and insert to new column -problem

Hi!

I have an algorithm that uses cursors to calculate difference between row and row-1 in a certain (int-type) column. How could I insert the difference value to this same table (or generate new dynamic table) as a new column?

I need this information to do some reporting with Reporting Services and show the difference there...

Thanks!

-Jukka

hi,

can you post your proc?|||

My knee-jerk reaction would be to suggest self-joining the table. This depends on whether you have a ready-made criteria that you can use either for keying or ordering your table. A simple-minded example might be something like:

declare @.xample table
( rowId integer primary key,
theValue numeric (5,2)
)
insert into @.xample
select number,
33.34*dbo.rand() + 33.33*dbo.rand() + 33.33*dbo.rand()
from master.dbo.spt_values
where name is null
and number <= 5

select a.rowId,
a.theValue as [A Value],
b.theValue as [B Value],
a.theValue - b.theValue as Difference
from @.xample a
join @.xample b
on a.rowId = b.rowId + 1

/*
rowId A Value B Value Difference
-- - - -
1 6.82 37.87 -31.05
2 20.28 6.82 13.46
3 35.15 20.28 14.87
4 56.60 35.15 21.45
5 35.21 56.60 -21.39
*/