December 5, 2008 at 12:23 pm
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:
SymbolIDMarketDatePriceVolume
11/1/2008$1 1000000
11/2/2008$1.25 1500000
…
10001/1/2008$11 2000000
10001/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:
SymbolIDMarketDateVolumeAvg1WkVolume
11311/03/083780138057833351.8
11311/04/084999877956755136.6
11311/05/084491516355071252.4
11311/06/084712994250102056.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!
December 5, 2008 at 1:38 pm
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
December 8, 2008 at 7:02 am
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
December 8, 2008 at 7:52 am
Here's a slightly different take on it:
[font="Courier New"]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
[/font]
This is just the weekly moving average, shouldn't be too hard to get the monthly into the same statement.
Cheers
ChrisM
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
December 8, 2008 at 9:00 am
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!
December 8, 2008 at 9:05 am
What's the name of your PK column in Test_HistoricalData?
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
December 8, 2008 at 9:09 am
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.
December 8, 2008 at 9:11 am
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
December 8, 2008 at 9:20 am
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
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
December 8, 2008 at 9:39 am
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.
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
December 8, 2008 at 10:03 am
I think I've got it now, I'm still really new to SQL and learning as I go...
Thanks again for all your help!
December 8, 2008 at 10:09 am
You're welcome, thanks for the feedback. As this is an unusual requirement - well I think it is - would you mind posting your final solution here perhaps with an explanation of why it was chosen?
Cheers
ChrisM
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
December 8, 2008 at 12:43 pm
No problem. Below is the solution that I ended up using:
-SOLUTION---------------------------------------------------------------------
UPDATE a
SET Avg1MoVol=(SELECT AVG(b.Volume)
FROM tblHistoricalData b
WHERE b.SymbolID = a.SymbolID AND b.MarketDate a.MarketDate-31)
FROM tblHistoricalData a
WHERE
a.MarketDate BETWEEN '2007-01-01' AND '2008-01-01'
GO
----------------------------------------------------------------------------------
I chose this solution because it ran the fastest when run on large sets of data. My table (tblHistoricalData) has over 13 million rows, representing the past four years of data on over 50,000 stocks.
For execution, I broke the dataset up according to date, and ran it for each year for both the monthly moving average and the weekly moving average.
Thanks again for all the help!
December 8, 2008 at 1:06 pm
Keep in mind that the BETWEEN statement you're using MIGHT double count some items. depending on how you store those values - it could be counting the first of each month in 2 separate "buckets". For example - in your case - the stuff from 8/1 might be getting counting in both the July bucket and the August one, too.
This could make some of your ocunting folks unhappy...:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 8, 2008 at 1:20 pm
I checked for double counting - and b/c of the way the MarketDate variable is stored, (as a datetime where the time is 4pm EST) there is no double counting.
Also, just for anyone using this - it uses a definition of a 'month' as 30 days which is generally accepted in financial scenarios - if you need a more specific month definition, using DIFFTIME would probably help.
But thanks for the heads-up! Always want to be aware of that kind of stuff 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply