a newbie question on derived/computed column

I am experimenting a simple stock database. Let's say it only contains
the end-of-day trading data for Intel. The database has the following
columns

date OpenPrice HighPrice LowPrice ClosePrice TradingVolume

For each trading date, I need to compute simple moving average based on
the last fifty days' ClosePrice. I guess I need to add a column named
SMA_50 and perform the computation to fill the column up.

To perform the computation, for each trading date, select the
ClosePrice of previous 50 trading days, add them up and divide the sum
by 50, I have to do this for each trading day.

Here are my specific questions:
1. Should I define the column SMA_50 as a Computed Column and use a
user defined function to perform the computation, or should I use other
approaches (such as triggers stored procedures)?

2. For each trading day, how can I select the ClosePrice of the last
fifty trading days.
Note that trading dates are not consecutive, weekends and holidays are
NOT trading days. It appears to be easy, but as an occasional user of
DB, I do not have an immediate solution.

Thank you very much for your input.lluum@.yahoo.com (lluum@.yahoo.com) writes:

Quote:

Originally Posted by

Here are my specific questions:
1. Should I define the column SMA_50 as a Computed Column and use a
user defined function to perform the computation, or should I use other
approaches (such as triggers stored procedures)?

Trigger or stored procedure is what I would pick. A computed column
that includes a UDF with data access is likely to give poor performance.

Quote:

Originally Posted by

2. For each trading day, how can I select the ClosePrice of the last
fifty trading days.
Note that trading dates are not consecutive, weekends and holidays are
NOT trading days. It appears to be easy, but as an occasional user of
DB, I do not have an immediate solution.

CREATE TABLE tradingdays (date datetime NOT NULL,
dayno int NOT NULL,
CONSTRAINT pk_tradingdays PRIMARY KEY (date),
CONSTRAINT u_tradingdays UNIQUE (dayno))

That is a table that maps a day to a number. The rest is easy.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

>
CREATE TABLE tradingdays (date datetime NOT NULL,
dayno int NOT NULL,
CONSTRAINT pk_tradingdays PRIMARY KEY (date),
CONSTRAINT u_tradingdays UNIQUE (dayno))
>
That is a table that maps a day to a number. The rest is easy.

I'm not following this.

- how would this table be populated and what with?
- how does the day number tell you that a day is not a weekend or
holiday?|||metaperl (metaperl@.gmail.com) writes:

Quote:

Originally Posted by

Erland Sommarskog wrote:

Quote:

Originally Posted by

>CREATE TABLE tradingdays (date datetime NOT NULL,
> dayno int NOT NULL,
> CONSTRAINT pk_tradingdays PRIMARY KEY (date),
> CONSTRAINT u_tradingdays UNIQUE (dayno))
>>
>That is a table that maps a day to a number. The rest is easy.

>
I'm not following this.
>
- how would this table be populated and what with?

Well, from somewhere you do get the information that a day is a trading
day or not. So you insert the trading day by some means, and for each
day you add, you increment the day numer.

Quote:

Originally Posted by

- how does the day number tell you that a day is not a weekend or
holiday?

It doesn't. The point is that you only add the trading days to the
table (whence the name). So to get the trading day 50 days ago from
today, you say:

SELECT a.date
FROM tradingdays a
JOIN tradingdays b ON a.dayno = b.dayno - 50
WHERE b.date = '20060711'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx