Select Previous Record in SQL Server 2008 -

here's case: have 1 table mytable contains 3 columns:

• id int, identity
• group varchar(2), not null
• value decimal(18,0), not null

table looks this:

id  group   value   prev_value  result ------------------------------------------ 1         20      0           20 2         30      20          10 3         35      30          5 4   b       100     0           100 5   b       150     100         50 6   b       300     200         100 7   c       40      0           40 8   c       60      40          20 9         50      35          15 10        70      50          20 

prev_value , result columns should custom columns. need make on view. can help? please... thank much.

the gist of need here join table itself, part of join condition value column of joined copy of table less value column of original. can group columns original table , select max value joined table results:

select t1.id, t1.[group], t1.value      , coalesce(max(t2.value),0) prev_value      , t1.value - coalesce(max(t2.value),0) result mytable t1 left join mytable t2 on t2.[group] = t1.[group] , t2.value < t1.value group t1.id, t1.[group], t1.value 

once can update sql server 2012 you'll able take advantage of new lag keyword.