Moving Average Over a Data Table Requiring Group

  • 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!

  • 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

  • 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

  • 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

    “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

  • 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!

  • 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

    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

  • 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.

  • 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

  • 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

    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

  • 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

    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

  • 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!

  • 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

    “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

  • 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!

  • 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?

  • 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