sql server - In sql divide row1 by row,row2 by row3.... . .and store the output third column -

i have table, need perform division on amt column , update data int calcamt

month  amt   calcamt jan   10000  null feb   20000  null mar   30000  null apr   40000  null 

eg: (feb/jan) store output in calcamt of feb, (mar/feb) store output in calcamt of mar,

expected output:

month  amt   calcamt jan   10000  0 feb   20000  2 mar   30000  1.5 apr   40000  1.33 

this can done through lead/lag, since using sql server 2008you cannot use lead/lag function. instead use can generate row number each row , self join same table row number + 1 fetch previous row data.

note: need add remaining months in order in case statement if have any.

as side note should not use reserved keywords object name ex: month

;with cte       (select rn=case [month]                       when 'jan' 1                       when 'feb' 2                       when 'mar' 3                       else 4                     end,                  *             yourtable)  select a.month,         a.amt,         calcamt = a.amt / nullif(b.amt ,0)   cte         left outer join cte b                      on a.rn = b.rn + 1  

• sqlfiddle demo

if want update table use this.

;with cte       (select rn=case [month]                       when 'jan' 1                       when 'feb' 2                       when 'mar' 3                       else 4                     end,                  *             yourtable)  update c  set    c.calcamt = d.calcamt    cte c         inner join (select a.month,                            a.amt,                            calcamt=a.amt / b.amt                       cte                            left outer join cte b                                         on a.rn = b.rn + 1) d                 on c.[month] = d.[month]