Get average of past 200 data from 2 table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Get average of past 200 data from 2 table

Author

Topic

gsnindia
Starting Member

5 Posts

Posted - 2005-07-11 : 01:33:25

we have two table
2004 has full year record
2005 has record till date

each year has new table

recordare stored in the

symbol,open,high,low,close,last,date

each contain year of that particular year

we want to retrive the average of last 200 days for this we mean some record from 2005 and some from 2004

we also want to retrive the average of last 50 days for this we mean some record from current year and some from last year

show them in as

currentdata, avergae50days, average200days

what will be the sql string to retrive such record

Seeking your help

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-07-11 : 02:27:21

You need to give more information
Read this
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-07-11 : 02:53:21

[code]
select
symbol, date, close,
(select avg(close)
from your_table t2
where t2.symbol= t1.symbol
and t2.date between dateadd(dd, -50, t1.date) and t1.date
)as moving_average
from your_table t1
[/code]

Now since I do this kind of stuff every day, let me tell you, I really wouldn't do this at the server. For technical analysis, it is quite common to look at several moving average intervals. Among the most commonly used is the 10-days, 30-days, and 200-days (or even 250 days) moving average. Suppose you have a portfolio of only 30 different stocks and assume that you surely don't want to analyse only one single stock isolated, but rather compare it to either some other stock or some other benchmark like an index. Guess what the performance will look like then? The fact that you have the data in two separate tables complicates things only. Just deliver your resultset to the client, and let them do this kind of analysis.

Just my $0.02 cents anyway.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

gsnindia
Starting Member

5 Posts

Posted - 2005-07-11 : 04:02:32

I have a software which will retreave data from the server and show into the software for all the stock

each table have about 1000 stocks and thier historical data

need to get the 200,50 moving average with stock name to show to user to selecting good stock

I hope this clear the question

i am keepking data in different table as year wise else the records goes in 20 lakh which may be give problem as the record increases

looking for better solution

gsnindia
Starting Member

5 Posts

Posted - 2005-07-11 : 09:28:44

date50 = Date - 50
date200 = Date - 200
sqr = ""
sqlsmt = "select symbol ,last, "
sqr = "(Select avg(last) from 2004 where symbol='ABB' and timpstamp > " & date50 & ") as av50,"
sqlsmt = sqlsmt & sqr
sqr = "(Select avg(last) from 2004 where symbol='ABB' And timpstamp > " & date200 & ") as avg200,"
sqlsmt = sqlsmt & sqr
sqlsmt = sqlsmt & " timpstamp from [2004] where symbol='ABB' order by timpstamp"

i am using the following but did not getresult even for single table

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-07-11 : 09:46:21

Your query is different from the one I've posted.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources