Finding the Average price for the past five days? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Finding the Average price for the past five days?

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-01-30 : 16:59:10

This one is stumping me...

I have a query that returns the a market close price of a given stock for many days.

Right now it just returns the price and date:

SELECT
DATE
PRICE

FROM
PRICES

WHERE
TICKER = 'IBM'

EXAMPLE:
DATE PRICE
01/02/2001 32.390
01/03/2001 37.500
01/04/2001 42.180
01/05/2001 41.290
01/08/2001 40.030
01/09/2001 42.850
01/10/2001 44.890
01/11/2001 47.230
01/12/2001 46.470

*** Note *** that there are no rows returned for days where the market was closed so date is not a reliable way to drive the new column values I'd like to have.

I'd like to add a column showing the average price for the current date and previous four days.

Example:

DATE PRICE AVG_FOR_FIVE_DAYS
01/02/2001 32.390 NULL
01/03/2001 37.500 NULL
01/04/2001 42.180 NULL
01/05/2001 41.290 NULL
01/08/2001 40.030 38.678
01/09/2001 42.850 40.77
01/10/2001 44.890 42.248
01/11/2001 47.230 43.258
01/12/2001 46.470 44.294

Any thoughts?

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2006-01-30 : 17:37:01

(Moved to the Transact-SQL forum.)

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-30 : 19:11:37

[code]select [DATE], PRICE, (select AVG(PRICE) from PRICES x
where x.[DATE] between (p.[DATE] - 5) and (p.[DATE] - 1)) as [Previous 4 Days]
from PRICES p[/code]

----------------------------------
'KH'

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-01-31 : 10:05:13

This is close, but it runs into the problem of weekends and holidays(no trading).

Example:

If you took the average using this query on a Tuesday, you'd only get FR,MON,TUE averaged together (due to 2 days on weekend).

mgandra
Starting Member

15 Posts

Posted - 2006-01-31 : 14:53:51

DECLARE @tmp TABLE (tran_date datetime,price money)
insert into @tmp values ('1/2/2006',30.0000)
insert into @tmp values ('1/3/2006',40.0000)
insert into @tmp values ('1/4/2006',33.0000)
insert into @tmp values ('1/5/2006',45.0000)
insert into @tmp values ('1/6/2006',31.0000)
insert into @tmp values ('1/9/2006',35.0000)
insert into @tmp values ('1/10/2006',40.0000)
insert into @tmp values ('1/11/2006',35.0000)
insert into @tmp values ('1/12/2006',50.0000)
insert into @tmp values ('1/13/2006',45.0000)

select p.tran_date, PRICE, (select AVG(PRICE) from @tmp x
where x.tran_date in
(select top 4 tran_date from @tmp where tran_date < p.tran_date order by tran_date desc))
from @tmp p

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-01-31 : 15:33:39

Is there a way to make the temp table more dynamic?

Maybe I need to populate all the data in a temp table as well as a row counter of some kind... then I might be able to drive off the row counter.

rav3n0u5
Yak Posting Veteran

88 Posts

Posted - 2006-02-01 : 00:25:43

DECLARE @tmp TABLE (id int identity, tran_date datetime, price money)

This will create a primary key that will auto-increment by 1. You can't insert a primary key unless you set indentity insert on.

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-01 : 12:20:56

Excellent! This works like a charm rav, thanks!

Here's what I ended up with

/*
These queries retrieve the AOL daily close price and the average price for the current to previous
20 days From 1/01/01 to the present.

Note a temp table is needed to provide reference numbers.
*/

DECLARE @DATE VARCHAR(12)
DECLARE @ID VARCHAR(12)
DECLARE @DAYS INT

/* Set your dynamics here to drive logic */

SET @DATE = '2001-01-01'
SET @ID = 'AOL'
SET @DAYS = 20

/* Create a temp table with row counters */
DECLARE @TMP TABLE (
ROW_COUNTER INT IDENTITY
, DATE_ DATETIME
, CLOSE_PRICE MONEY
)

/* Populate the temp table with pricing data */
INSERT INTO @TMP
SELECT
B.DATE_
, B.CLOSE_
FROM PRCINFO3 A
, PRCDLY B
WHERE A.ID = @ID
AND A.CODE = B.CODE
AND B.DATE_ >= @DATE
ORDER BY
B.DATE_

/* Pull the report data from the temp file */
SELECT
A.DATE_
, A.CLOSE_PRICE
, (
SELECT CASE
WHEN A.ROW_COUNTER < 20
THEN 'Null'
ELSE
(
SELECT
CONVERT(VARCHAR(20),AVG(B.CLOSE_PRICE))
FROM
@TMP B
WHERE
B.ROW_COUNTER BETWEEN (A.ROW_COUNTER - @DAYS + 1)
AND A.ROW_COUNTER
)
END
) AS MvgAvg
FROM
@TMP A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources