Average Value based previous record Avg Value

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  October 9, 2012 at 9:16 pm

  #282982

  Hi Experts,

  Here is another scenario,th some sampe data

  CREATE TABLE #sample

  (

  id int identity ,

  weight float

  )

  insert into #sample

  select 10 union all

  select 30 union all

  select 20 union all

  select 50.5 union all

  select 100 union all

  select 81

  select * from #sample

  -- e

  expected output:

  I need to calculate the average of weight based on previous records Avg value.

  formula for avg_current_prevoius is (previous average value+current weight value)/2.0

  idweight avg_current_prevoius

  110 10

  230 20

  320 20

  450.5 35.2500

  5100 67.225

  681 74.312500

  Please help me ..

• Lokesh Vij

  SSChampion

  Points: 10836

  More actions

  October 9, 2012 at 10:17 pm

  #1547442

  Here is the query:

  ;WITH tempavg

  AS (SELECT id,

  weight,

  weight avg_current_prevoius

  FROM #sample

  WHERE id = 1

  UNION ALL

  SELECT a.id,

  a.weight,

  .5 * ( a.weight + b.avg_current_prevoius )

  FROM #sample a

  INNER JOIN tempavg b

  ON b.id + 1 = a.id)

  SELECT *

  FROM tempavg;

  ~ Lokesh Vij
  

  ---

  Guidelines for quicker answers on T-SQL question[/url]
  Guidelines for answers on Performance questions

  Link to my Blog Post --> www.SQLPathy.com[/url]

  Follow me @Twitter