Assuming your [date] column is a DATETIME datatype then this would work. Looks like your sample data is wrong? 73(125-15). Shouldn't that be 110?
declare @yourTable table (date datetime, startVol int, endVol int, diff int)
insert @yourTable
select '08/01/2012', 100, 15, 85 union all
select '08/02/2012', 125, 27, 98 union all
select '08/03/2012', 107, 42, 65
;with cte
as
(
select date
,startVol
,endVol
,diff
,row_number() over (order by date) as rn
from @yourTable
)
select a.*, isNull(a.startVol - b.endVol,0) as [import(New Field)]
from cte a
left outer join cte b on b.rn = a.rn-1
OUTPUT:
date startVol endVol diff rn import(New Field)
----------------------- ----------- ----------- ----------- -------------------- -----------------
2012-08-01 00:00:00.000 100 15 85 1 0
2012-08-02 00:00:00.000 125 27 98 2 110
2012-08-03 00:00:00.000 107 42 65 3 80
Be One with the Optimizer
TG