Moving Average Over a Data Table Requiring Group

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Moving Average Over a Data Table Requiring...

15 posts, Page 1 of 2

1 2 »»

Moving Average Over a Data Table Requiring Group

Rate Topic

Display Mode

Topic Options

Author

Message

bkmooney

Posted Friday, December 05, 2008 12:23 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 2:08 PM
Points: 74, Visits: 237

I have a table which contains historical stock market information for a large number of stocks and over a period of time.

Each row represents a single stock on one day, so they look something like:
SymbolID MarketDate Price Volume
1 1/1/2008 $1 1000000
1 1/2/2008 $1.25 1500000
…
1000 1/1/2008 $11 2000000
1000 1/2/2008 $13 2220000
…

I'd like to add a column that represents the moving average volume for each stock over the previous week, but am running into a bit of trouble.

I have a select statement that allows me to create such a column for a single stock, but can't seem to figure out how to get it to produce it for all stocks - I think I need to remove my SymbolID restriction and add a "group by", but SQL keeps telling me there is an error returning too many values to a subquery.

What I have so far is below - it is neither elegant nor a complete solution - so any help I could get to get me closer to elegant and/or complete is greatly appreciated!

select X.SymbolID
, X.MarketDate
, X.Volume
, (SELECT AVG(H.Volume)
FROM tblHistoricalData H
WHERE H.SymbolID=113 AND H.MarketDate BETWEEN DATEADD(dd, -7, X.MarketDate) and X.MarketDate-1
) AS Avg1WkVolume

from tblHistoricalData X

where
X.SymbolID=113 AND X.MarketDate BETWEEN '2008-01-01' and GetDate()

order by
X.MarketDate
GO

This produces output like:
SymbolID MarketDate Volume Avg1WkVolume
113 11/03/08 37801380 57833351.8
113 11/04/08 49998779 56755136.6
113 11/05/08 44915163 55071252.4
113 11/06/08 47129942 50102056.6
...

but I want it to be able to do this for all SymbolID's - not just 1 at a time. I have over 10,000 of them.

Thanks!

Post #614894

dmc-608719

Posted Friday, December 05, 2008 1:38 PM

Old Hand

Group: General Forum Members
Last Login: Monday, April 23, 2012 7:35 AM
Points: 373, Visits: 761

Maybe you can do something with a CTE like the following...

DECLARE @Tbl AS TABLE (
Symbol INT,
MarketDate DATETIME,
Price DECIMAL(10,2),
Volume INT
)

INSERT INTO @Tbl
VALUES (1, '1/1/2008', 1.00, 100000)

INSERT INTO @Tbl
VALUES (1, '1/2/2008', .98, 105000)

INSERT INTO @Tbl
VALUES (1, '1/3/2008', 1.01, 110000)

INSERT INTO @Tbl
VALUES (1, '1/4/2008', 1.02, 120000)

INSERT INTO @Tbl
VALUES (1, '1/5/2008', 1.12, 125000)

INSERT INTO @Tbl
VALUES (1, '1/6/2008', 1.14, 135000)

INSERT INTO @Tbl
VALUES (1, '1/7/2008', 1.10, 150000)

INSERT INTO @Tbl
VALUES (1, '1/8/2008', 1.12, 100000)

INSERT INTO @Tbl
VALUES (1, '1/9/2008', 1.12, 175000)

INSERT INTO @Tbl
VALUES (2, '1/2/2008', 1.22, 1000100)

INSERT INTO @Tbl
VALUES (2, '1/5/2008', 1.24, 1123000)

INSERT INTO @Tbl
VALUES (2, '1/7/2008', 1.25, 1085000)

INSERT INTO @Tbl
VALUES (2, '1/17/2008', 1.25, 9999999);

--SELECT *
--FROM @Tbl;

WITH WeekTable (Symbol, MarketDate, Volume)
AS
(
SELECT T1.Symbol,
T1.MarketDate,
T2.Volume
FROM @Tbl T1
INNER JOIN @Tbl T2 ON
(T1.Symbol = T2.Symbol
AND T2.MarketDate <= T1.MarketDate
AND T2.MarketDate > DATEADD(WEEK, -1, T1.MarketDate))
)

SELECT Symbol,
MarketDate,
AVG(Volume) [RollVol]
FROM WeekTable
GROUP BY Symbol, MarketDate

Post #614949

bkmooney

Posted Monday, December 08, 2008 7:02 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 2:08 PM
Points: 74, Visits: 237

Thanks for the help - I used your solution and then ended up fixing my own and coming up with another.
I'm posting the solutions below...

These solutions all calculate both a weekly and monthly moving average. Here are the solutions:

-SOLUTION 1--------------------------------------------------------------------------------------

SELECT X.SymbolID
, X.MarketDate
, X.Volume
, (SELECT AVG(H.Volume)
FROM tblHistoricalData H
WHERE H.SymbolID=X.SymbolID AND H.MarketDate BETWEEN DATEADD(dd, -7, X.MarketDate) AND X.MarketDate-1
) AS Avg1WkVolume
, (SELECT AVG(Y.Volume)
FROM tblHistoricalData Y
WHERE Y.SymbolID=X.SymbolID and Y.MarketDate BETWEEN DATEADD(mm,-1, X.MarketDate) AND X.MarketDate-1
) AS Avg1MoVolume

FROM tblHistoricalData X

WHERE
X.MarketDate BETWEEN '2008-11-07' and GetDate()
AND X.SymbolID<=1000

GROUP BY
X.SymbolID, X.MarketDate, X.Volume

ORDER BY
X.SymbolID, X.MarketDate
GO

-SOLUTION 2--------------------------------------------------------------------------------------

WITH
VolTable (SymbolID, MarketDate, WkVol, MoVol)
AS
(SELECT
T1.SymbolID,
T1.MarketDate,
T2.Volume as WkVol,
T3.Volume as MoVol
FROM tblHistoricalData T1
INNER JOIN tblHistoricalData T2 ON
(T1.SymbolID = T2.SymbolID
AND T2.MarketDate+1 <= T1.MarketDate
AND T2.MarketDate+1 > DATEADD(WEEK, -1, T1.MarketDate))
INNER JOIN tblHistoricalData T3 ON
(T1.SymbolID = T3.SymbolID
AND T3.MarketDate+1 <= T1.MarketDate
AND T3.MarketDate+1 > DATEADD(MONTH, -1, T1.MarketDate))
)

SELECT SymbolID,
MarketDate,
AVG(WkVol) as Avg1WkVol,
AVG(MoVol) as Avg1MoVol

FROM VolTable

WHERE SymbolID<=1000
AND MarketDate BETWEEN '2008-11-07' AND GETDATE()

GROUP BY SymbolID, MarketDate

ORDER BY
SymbolID, MarketDate

GO

-SOLUTION 3- AKA BRUTE FORCE AND A LOT OF LEFT JOINS---------------------------------------------

SELECT
H.SymbolID
, H.MarketDate
, H.Volume
, ((CASE WHEN(H1.Volume IS NULL) THEN 0 ELSE H1.Volume END)+
(CASE WHEN(H2.Volume IS NULL) THEN 0 ELSE H2.Volume END)+
(CASE WHEN(H3.Volume IS NULL) THEN 0 ELSE H3.Volume END)+
(CASE WHEN(H4.Volume IS NULL) THEN 0 ELSE H4.Volume END)+
(CASE WHEN(H5.Volume IS NULL) THEN 0 ELSE H5.Volume END)+
(CASE WHEN(H6.Volume IS NULL) THEN 0 ELSE H6.Volume END)+
(CASE WHEN(H7.Volume IS NULL) THEN 0 ELSE H7.Volume END))/
NULLIF(((CASE WHEN(H1.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H2.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H3.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H4.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H5.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H6.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H7.Volume IS NULL) THEN 0 ELSE 1 END)),0) as Avg1WkVol
, ((CASE WHEN(H1.Volume IS NULL) THEN 0 ELSE H1.Volume END)+
(CASE WHEN(H2.Volume IS NULL) THEN 0 ELSE H2.Volume END)+
(CASE WHEN(H3.Volume IS NULL) THEN 0 ELSE H3.Volume END)+
(CASE WHEN(H4.Volume IS NULL) THEN 0 ELSE H4.Volume END)+
(CASE WHEN(H5.Volume IS NULL) THEN 0 ELSE H5.Volume END)+
(CASE WHEN(H6.Volume IS NULL) THEN 0 ELSE H6.Volume END)+
(CASE WHEN(H7.Volume IS NULL) THEN 0 ELSE H7.Volume END)+
(CASE WHEN(H8.Volume IS NULL) THEN 0 ELSE H8.Volume END)+
(CASE WHEN(H9.Volume IS NULL) THEN 0 ELSE H9.Volume END)+
(CASE WHEN(H10.Volume IS NULL) THEN 0 ELSE H10.Volume END)+
(CASE WHEN(H11.Volume IS NULL) THEN 0 ELSE H11.Volume END)+
(CASE WHEN(H12.Volume IS NULL) THEN 0 ELSE H12.Volume END)+
(CASE WHEN(H13.Volume IS NULL) THEN 0 ELSE H13.Volume END)+
(CASE WHEN(H14.Volume IS NULL) THEN 0 ELSE H14.Volume END)+
(CASE WHEN(H15.Volume IS NULL) THEN 0 ELSE H15.Volume END)+
(CASE WHEN(H16.Volume IS NULL) THEN 0 ELSE H16.Volume END)+
(CASE WHEN(H17.Volume IS NULL) THEN 0 ELSE H17.Volume END)+
(CASE WHEN(H18.Volume IS NULL) THEN 0 ELSE H18.Volume END)+
(CASE WHEN(H19.Volume IS NULL) THEN 0 ELSE H19.Volume END)+
(CASE WHEN(H20.Volume IS NULL) THEN 0 ELSE H20.Volume END)+
(CASE WHEN(H21.Volume IS NULL) THEN 0 ELSE H21.Volume END)+
(CASE WHEN(H22.Volume IS NULL) THEN 0 ELSE H22.Volume END)+
(CASE WHEN(H23.Volume IS NULL) THEN 0 ELSE H23.Volume END)+
(CASE WHEN(H24.Volume IS NULL) THEN 0 ELSE H24.Volume END)+
(CASE WHEN(H25.Volume IS NULL) THEN 0 ELSE H25.Volume END)+
(CASE WHEN(H26.Volume IS NULL) THEN 0 ELSE H26.Volume END)+
(CASE WHEN(H27.Volume IS NULL) THEN 0 ELSE H27.Volume END)+
(CASE WHEN(H28.Volume IS NULL) THEN 0 ELSE H28.Volume END)+
(CASE WHEN(H29.Volume IS NULL) THEN 0 ELSE H29.Volume END)+
(CASE WHEN(H30.Volume IS NULL) THEN 0 ELSE H30.Volume END))/
NULLIF(((CASE WHEN(H1.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H2.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H3.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H4.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H5.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H6.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H7.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H8.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H9.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H10.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H11.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H12.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H13.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H14.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H15.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H16.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H17.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H18.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H19.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H20.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H21.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H22.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H23.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H24.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H25.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H26.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H27.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H28.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H29.Volume IS NULL) THEN 0 ELSE 1 END)+
(CASE WHEN(H30.Volume IS NULL) THEN 0 ELSE 1 END)),0) as Avg1MoVol

FROM
tblHistoricalData H
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H1 ON H.SymbolID=H1.SymbolID AND H.MarketDate=H1.MarketDate+1
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H2 ON H.SymbolID=H2.SymbolID AND H.MarketDate=H2.MarketDate+2
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H3 ON H.SymbolID=H3.SymbolID AND H.MarketDate=H3.MarketDate+3
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H4 ON H.SymbolID=H4.SymbolID AND H.MarketDate=H4.MarketDate+4
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H5 ON H.SymbolID=H5.SymbolID AND H.MarketDate=H5.MarketDate+5
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H6 ON H.SymbolID=H6.SymbolID AND H.MarketDate=H6.MarketDate+6
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H7 ON H.SymbolID=H7.SymbolID AND H.MarketDate=H7.MarketDate+7
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H8 ON H.SymbolID=H8.SymbolID AND H.MarketDate=H8.MarketDate+8
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H9 ON H.SymbolID=H9.SymbolID AND H.MarketDate=H9.MarketDate+9
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H10 ON H.SymbolID=H10.SymbolID AND H.MarketDate=H10.MarketDate+10
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H11 ON H.SymbolID=H11.SymbolID AND H.MarketDate=H11.MarketDate+11
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H12 ON H.SymbolID=H12.SymbolID AND H.MarketDate=H12.MarketDate+12
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H13 ON H.SymbolID=H13.SymbolID AND H.MarketDate=H13.MarketDate+13
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H14 ON H.SymbolID=H14.SymbolID AND H.MarketDate=H14.MarketDate+14
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H15 ON H.SymbolID=H15.SymbolID AND H.MarketDate=H15.MarketDate+15
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H16 ON H.SymbolID=H16.SymbolID AND H.MarketDate=H16.MarketDate+16
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H17 ON H.SymbolID=H17.SymbolID AND H.MarketDate=H17.MarketDate+17
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H18 ON H.SymbolID=H18.SymbolID AND H.MarketDate=H18.MarketDate+18
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H19 ON H.SymbolID=H19.SymbolID AND H.MarketDate=H19.MarketDate+19
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H20 ON H.SymbolID=H20.SymbolID AND H.MarketDate=H20.MarketDate+20
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H21 ON H.SymbolID=H21.SymbolID AND H.MarketDate=H21.MarketDate+21
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H22 ON H.SymbolID=H22.SymbolID AND H.MarketDate=H22.MarketDate+22
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H23 ON H.SymbolID=H23.SymbolID AND H.MarketDate=H23.MarketDate+23
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H24 ON H.SymbolID=H24.SymbolID AND H.MarketDate=H24.MarketDate+24
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H25 ON H.SymbolID=H25.SymbolID AND H.MarketDate=H25.MarketDate+25
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H26 ON H.SymbolID=H26.SymbolID AND H.MarketDate=H26.MarketDate+26
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H27 ON H.SymbolID=H27.SymbolID AND H.MarketDate=H27.MarketDate+27
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H28 ON H.SymbolID=H28.SymbolID AND H.MarketDate=H28.MarketDate+28
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H29 ON H.SymbolID=H29.SymbolID AND H.MarketDate=H29.MarketDate+29
LEFT JOIN (
SELECT SymbolID, MarketDate, Volume FROM tblHistoricalData
) AS H30 ON H.SymbolID=H30.SymbolID AND H.MarketDate=H30.MarketDate+30

WHERE
H.SymbolID<=1000
AND H.MarketDate BETWEEN '2008-11-07' AND GETDATE()

GROUP BY
H.SymbolID, H.MarketDate, H.Volume, H1.SymbolID, H1.MarketDate, H1.Volume, H2.MarketDate, H2.Volume, H3.MarketDate, H3.Volume, H4.MarketDate, H4.Volume,
H5.MarketDate, H5.Volume, H6.MarketDate, H6.Volume, H7.MarketDate, H7.Volume, H8.MarketDate, H8.Volume, H9.MarketDate, H9.Volume, H10.MarketDate, H10.Volume,
H11.MarketDate, H11.Volume, H12.MarketDate, H12.Volume, H13.MarketDate, H13.Volume, H14.MarketDate, H14.Volume, H15.MarketDate, H15.Volume, H16.MarketDate,
H16.Volume, H17.MarketDate, H17.Volume, H18.MarketDate, H18.Volume, H19.MarketDate, H19.Volume, H20.MarketDate, H20.Volume, H21.MarketDate, H21.Volume,
H22.MarketDate, H22.Volume, H23.MarketDate, H23.Volume, H24.MarketDate, H24.Volume, H25.MarketDate, H25.Volume, H26.MarketDate, H26.Volume, H27.MarketDate,
H27.Volume, H28.MarketDate, H28.Volume, H29.MarketDate, H29.Volume, H30.MarketDate, H30.Volume

ORDER BY
H.SymbolID, H.MarketDate
GO

Post #615512

ChrisM@Work

Posted Monday, December 08, 2008 7:52 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140

Here's a slightly different take on it:

SET DATEFORMAT MDY
DECLARE @Tbl TABLE (
        Symbol INT,
        MarketDate DATETIME,
        Price DECIMAL(10,2),
        Volume INT
        )

INSERT INTO @Tbl
VALUES (1, '1/1/2008', 1.00, 1)

INSERT INTO @Tbl
VALUES (1, '1/2/2008', .98, 10)

INSERT INTO @Tbl
VALUES (1, '1/3/2008', 1.01, 100)

INSERT INTO @Tbl
VALUES (1, '1/4/2008', 1.02, 1000)

INSERT INTO @Tbl
VALUES (1, '1/5/2008', 1.12, 10000)

INSERT INTO @Tbl
VALUES (1, '1/6/2008', 1.14, 100000)

INSERT INTO @Tbl
VALUES (1, '1/7/2008', 1.10, 1000000)

INSERT INTO @Tbl
VALUES (1, '1/8/2008', 1.12, 100000)

INSERT INTO @Tbl
VALUES (1, '1/9/2008', 1.12, 175000)

INSERT INTO @Tbl
VALUES (2, '1/2/2008', 1.22, 1000100)

INSERT INTO @Tbl
VALUES (2, '1/5/2008', 1.24, 1123000)

INSERT INTO @Tbl
VALUES (2, '1/7/2008', 1.25, 1085000)

INSERT INTO @Tbl
VALUES (2, '1/17/2008', 1.25, 9999999);

-- Easy test
SELECT a.*, (SELECT SUM(Volume) FROM @Tbl b WHERE b.Symbol = a.Symbol AND b.MarketDate < a.MarketDate AND b.MarketDate > a.MarketDate-8)
FROM @Tbl a
ORDER BY Symbol, MarketDate DESC

-- Run
SELECT a.*, (SELECT AVG(Volume) FROM @Tbl b WHERE b.Symbol = a.Symbol AND b.MarketDate < a.MarketDate AND b.MarketDate > a.MarketDate-8)
FROM @Tbl a
ORDER BY Symbol, MarketDate DESC

This is just the weekly moving average, shouldn't be too hard to get the monthly into the same statement.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #615542

bkmooney

Posted Monday, December 08, 2008 9:00 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 2:08 PM
Points: 74, Visits: 237

Thanks for the help! This way seems to be the most efficient so far... so I tried incorporating it into an "Update" to populate a column called: Avg1WkVol - but am running into some errors.

Here is what I ran:

UPDATE Test_HistoricalData a

SET Avg1WkVol=(SELECT AVG(b.Volume)
FROM Test_HistoricalData b
WHERE b.SymbolID = a.SymbolID AND b.MarketDate a.MarketDate-8)

WHERE
a.SymbolID=113 --(I wanted to try it on one symbol first.)

GO

And I got back syntax errors:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'a'.
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'WHERE'.

Any ideas?

Thanks again!

Post #615594

ChrisM@Work

Posted Monday, December 08, 2008 9:05 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140

What's the name of your PK column in Test_HistoricalData?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #615601

bkmooney

Posted Monday, December 08, 2008 9:09 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 2:08 PM
Points: 74, Visits: 237

Test_HistoricalData is just a copy of another table, I wanted to make sure I had this running correctly before making changes to my real table. I didn't copy over any of the indexes, so Test_HistoricalData does not have a PK.

In the actual table I will be using once everything works, there is a PK called RowID, which is just an integer.

Post #615604

bkmooney

Posted Monday, December 08, 2008 9:11 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 2:08 PM
Points: 74, Visits: 237

Sorry, looks like the code didn't paste correctly the first time - it read the 'less than' and 'greater than' as HTML brackets. Should be:

UPDATE Test_HistoricalData a

SET Avg1WkVol=(SELECT AVG(b.Volume)
FROM Test_HistoricalData b
WHERE b.SymbolID = a.SymbolID AND b.MarketDate < a.MarketDate AND b.MarketDate > a.MarketDate-8)

WHERE
a.SymbolID=113

GO

Post #615609

ChrisM@Work

Posted Monday, December 08, 2008 9:20 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140

Try this:

UPDATE h SET Avg1WkVol = d.Avg1WkVol
FROM Test_HistoricalData h
INNER JOIN (SELECT a.RowID, Avg1WkVol = (SELECT AVG(Volume) 
		FROM Test_HistoricalData b 
		WHERE b.Symbol = a.Symbol 
		AND b.MarketDate < a.MarketDate 
		AND b.MarketDate > a.MarketDate-8)
	FROM Test_HistoricalData a
) d ON d.RowID = h.RowID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #615619

ChrisM@Work

Posted Monday, December 08, 2008 9:39 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140

Or better still:
UPDATE a SET Avg1WkVol = (SELECT AVG(Volume) from Test_HistoricalData b where b.Symbol = a.Symbol AND b.MarketDate a.MarketDate-8)
FROM Test_HistoricalData a

Although the previous version is much easier to disassemble for testing.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #615635

« Prev Topic | Next Topic »

15 posts, Page 1 of 2

1 2 »»

Permissions