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