0

I'm very very new to sql server. I want to have a table with intra-day stock information and analytical calculation (ex. Simple Moving Average - SMA).

I've designed a table that will contain market data and imported the data to it. now I want to add the SMA to the table and save it. next day I want to add market new data and calculate based on last SMA figures of the previous day and save again to the same table.

Is it going this way in sql server or I should have a query to get the SMA and then a way to save the results to the source table because I want it in one table and not to execute the query again for the same data!

what I mean, if I don't save MVA results figures to a table (preferably source table) I believe query and calculation must be done for old data again plus the new one to get analysis for new day which is not practical.

Please guide me to the right way! and I would appreciate if it's in detail(How!).

more detail-- I'm using sql server 2008. I got all these results easily with excel and vba but because of the huge data I need to move to sql server.

Adnan Al-Husain
  • 110
  • 1
  • 3
  • 17

1 Answers1

0

Create user defined function to compute SMA and use this function as a value to computed column in the table .

sudo code - this is just an idea - I have not tested:

CREATE FUNCTION dbo.GetSMAValue(INT @price, INT @date)
RETURNS IN
AS 
  SELECT (@recid + sma) / no-of-days --whatever the formula for SMA
  FROM price_table 
  WHERE date = dateadd (day,-1, @date)


ALTER TABLE price_table 
ADD sma AS dbo.GetSMAValue(price, date)
BenMorel
  • 34,448
  • 50
  • 182
  • 322
Bala
  • 4,427
  • 6
  • 26
  • 29
  • thanks brother.I've read here http://msdn.microsoft.com/en-us/library/ms189292.aspx#BKMK_persisted that I can't use aggregate function to execute computed column, will user defined function solve this? – Adnan Al-Husain Jun 18 '12 at 09:02
  • It's really nice to have a trial code but I'd like 1st to have a literal direction of the best way to have the data with the analysis saved in the same table!!. would I be able to use user defined function for computed column in the same table structure or I should go for querying with user defined function and move results to accumulative main table? – Adnan Al-Husain Jun 18 '12 at 23:15
  • still i havn't solved yet but i believe this is so great..thanks my brother. – Adnan Al-Husain Jul 14 '12 at 05:37