Calculating 4 Quarter Moving Averages by Security

Question

To simplify my question, let's say I have a table of quarterly dates and sales numbers by security.

month_end_date, ID, Sales

I need to calculate 4 quarter moving averages of Sales per ID. I tried to do the following:

-- CALCULATE FOUR QUARTER AVERAGES
-- CREATE INDEX OF DATES
SELECT *, date_idx=RANK() OVER(PARTITION BY ID ORDER BY month_end_date)
INTO #Temp_Date_Index
FROM #Temp_industrials
GO
CREATE INDEX idx_1 on #Temp_Date_Index(ID, date_idx)
GO

-- CALCULATE MOVING AVERAGE
SELECT MAX(month_end_date), ID, Sales_avg=AVG(Sales)
INTO #Temp_4Q_Avgs
FROM #Temp_Date_Index
WHERE date_idx>=4 AND (date_idx BETWEEN date_idx AND (date_idx-4))
GROUP BY ID

The first step to create an index of dates worked fine but the "CALCULATE MOVING AVERAGE" part gives me an error "An object or column name is missing or empty". If I take out the MAX(month_end_date) from the SELECT line it runs without error but gives me an empty table result.

Perhaps my approach is fundamentally flawed.

Thanks in advance for any help.

Also tried using a subquery:

SELECT end_of_period=MAX(month_end_date), ID, 
op_inc_avg=AVG(
SELECT r.Sales
FROM #Temp_Date_Index r
WHERE r.date_idx BETWEEN l.date_idx AND (l.date_idx-3)
)

FROM #Temp_Date_Index l WHERE r.date_idx>=4 GROUP BY ID

but I'm getting

Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'SELECT'. Msg 102, Level 15, State 1, Line 6 Incorrect syntax near ')'.

Answer 1

My colleague showed me how to do it:

-- CALCULATE FOUR QUARTER AVERAGES
-- CREATE INDEX OF DATES
DROP TABLE #Temp_Date_Index
GO
SELECT *, date_idx=RANK() OVER(PARTITION BY gvkey ORDER BY month_end_date)
INTO #Temp_Date_Index
FROM #Temp_industrials
GO
CREATE INDEX idx_1 on #Temp_Date_Index(gvkey, date_idx)
GO

-- CALCULATE MOVING AVERAGE
DROP TABLE #Temp_4Q_Avgs
GO
SELECT l.gvkey, l.date_idx, mov_avg=AVG(r.op_inc_ratio)
INTO #Temp_4Q_Avgs
FROM #Temp_Date_Index l, #Temp_Date_Index r
WHERE r.gvkey=l.gvkey AND (r.date_idx BETWEEN (l.date_idx-3) AND l.date_idx)AND        r.date_idx>=4
GROUP BY l.gvkey, l.date_idx
GO
CREATE INDEX idx_1 on #Temp_4Q_Avgs(month_end_date, gvkey)
GO

Hope someone else can benefit.