Difficult Stored Procedure Question!!!!

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 2005 Forums

Transact-SQL (2005)

Difficult Stored Procedure Question!!!!

Author

Topic

cjsteury2
Starting Member

14 Posts

Posted - 2009-10-24 : 15:36:11

Hi, I need to make a (4) calculations in a stored procedure and am having a hard time getting the data to show up.
Help is GREATLY appreciated!!! I really need help with how to construct and get sp output for variables using the formulas as follows:

Tickers (Table):
date datetime
ticker nvarhchar
close decimal(7,2)

Output:
@MvAvg decimal(7,2)
@StDev decimal(7,2)
@BBUp decimal(7,2)
@BBDown decimal(7,2)

1. The Moving Average formula for @MvAvg is:

SELECT CONVERT(VARCHAR(20), X.[date], 101) DATE, AVG(Y.[CLOSE])MvAvg
FROM TICKERS X, TICKERS Y
WHERE X.[DATE] > (SELECT TOP 1 Z.[DATE] FROM TICKERS Z WHERE Z.TICKER = 'EBAY' ORDER BY Z.DATE ASC)+20
AND X.TICKER = 'EBAY' AND Y.TICKER = 'EBAY'
AND X.[DATE] BETWEEN Y.[DATE]-20 AND Y.[DATE]
GROUP BY X.DATE
ORDER BY X.DATE

2. The Standard Deviation formula for @StDev is:

SELECT CONVERT(VARCHAR(20), X.[date], 101) DATE, STDEV(Y.[CLOSE])STDEV
FROM TICKERS X, TICKERS Y
WHERE X.[DATE] > (SELECT TOP 1 Z.[DATE] FROM TICKERS Z WHERE Z.TICKER = 'EBAY' ORDER BY Z.DATE ASC)+20
AND X.TICKER = 'EBAY' AND Y.TICKER = 'EBAY'
AND X.[DATE] BETWEEN Y.[DATE]-20 AND Y.[DATE]
GROUP BY X.DATE
ORDER BY X.DATE

3. The BollingerBands Upper for @BBUp is:

@BBUp = @MvAvg + (2 * @StDev)

4. The BollingerBands Lower for @BBDown is:

@BBDown = @MvAvg - (2 * StDev)

I have been having a HECK of a time writing a stored procedure to calculate the values... The is as far as I've been able to get, but can not figure how to actually store the values for @MvAvg and @StDev so that I can calculate the outcome as @BBUp & @BBDown...

SELECT CONVERT(VARCHAR(20), X.[date], 101) DATE
, AVG(Y.[CLOSE])MOV_AVG
, STDEV(Y.[CLOSE])STD_DEV
FROM TICKERS X, TICKERS Y
WHERE X.[DATE] > (SELECT TOP 1 Z.[DATE] FROM TICKERS Z WHERE Z.TICKER = 'EBAY' ORDER BY Z.DATE ASC)+20
AND X.TICKER = 'EBAY' AND Y.TICKER = 'EBAY'
AND X.[DATE] BETWEEN Y.[DATE]-20 AND Y.[DATE]
GROUP BY X.DATE
ORDER BY X.DATE

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-10-26 : 10:55:25

will you be using these values quiet often , your best bet is to create a nice function that will return those values.
declare @movingAverage varchar(50)

Select @movingAverage = fnMovingAverage()
FROM xTable where etc

or to work it as you have it I would do the following, you can assign the variable right there in the the select statement

SELECT @movingAverage = CONVERT(VARCHAR(20), X.[date], 101) DATE, AVG(Y.[CLOSE])MvAvg
FROM TICKERS X, TICKERS Y
WHERE X.[DATE] > (SELECT TOP 1 Z.[DATE] FROM TICKERS Z WHERE Z.TICKER = 'EBAY' ORDER BY Z.DATE ASC)+20
AND X.TICKER = 'EBAY' AND Y.TICKER = 'EBAY'
AND X.[DATE] BETWEEN Y.[DATE]-20 AND Y.[DATE]
GROUP BY X.DATE
ORDER BY X.DATE

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources