computing column values

• alen teplitsky

  SSC-Dedicated

  Points: 30014

  More actions

  December 3, 2006 at 12:33 pm

  #115196

  I have some data that I downloaded to learn SQL. I downloaded some stock market data from Yahoo. The basic data is the date, open, close, high, low and the volume.

  I added some more columns that I want to populate by computing the data that I downloaded. Change, percent change, 10 day moving average, 50 day moving average, 200 day moving average.

  what is the best way to calculate the columns where i need the average? It's easy doing it for the last few months, but say i wanted to do it programatically for the 20,000 rows or so I have in each table?

• Jambu Krishnamurthy

  SSCrazy

  Points: 2054

  More actions

  December 4, 2006 at 9:28 am

  #675527

  is this what you are looking for, or something more involved. If so pl give some examples of data, (input and output.)

   

  create table t1 (f1 int, f2 int, f3 int, f1Avg int)

  declare @var int

  set @var = 1

  while @var <= 20000

  begin

      insert into t1 values(@var,null,null,null)

      set @var = @var + 1

  end

  declare @avg int

  set @avg = (select avg(f1) from t1)

  update t1 set f1Avg = @avg