Adding Staggered Running Total And Average To Query

Hi,

I am trying to add a staggered running total and average to a query
returning quarterly CPI data. I need to add 4 quarterly data points
together to calculate a moving 12-month sum (YrCPI), and then to
complicate things, calculate a moving average of the 12-month figure
(AvgYrCPI).

Given the sample data:

CREATE TABLE [dbo].[QtrInflation] (
[Qtr] [smalldatetime] NOT NULL ,
[CPI] [decimal](8, 4) NOT NULL
) ON [PRIMARY]
GO

INSERT INTO QtrInflation (Qtr, CPI)
SELECT '1960-03-01', 0.7500 UNION
SELECT '1960-06-01', 1.4800 UNION
SELECT '1960-09-01', 1.4600 UNION
SELECT '1960-12-01', 0.7200 UNION
SELECT '1961-03-01', 0.7100 UNION
SELECT '1961-06-01', 0.7100 UNION
SELECT '1961-09-01',-0.7000 UNION
SELECT '1961-12-01', 0.0000 UNION
SELECT '1962-03-01', 0.0000 UNION
SELECT '1962-06-01', 0.0000 UNION
SELECT '1962-09-01', 0.0000 UNION
SELECT '1962-12-01', 0.0000 UNION
SELECT '1963-03-01', 0.0000 UNION
SELECT '1963-06-01', 0.0000 UNION
SELECT '1963-09-01', 0.7100 UNION
SELECT '1963-12-01', 0.0000 UNION
SELECT '1964-03-01', 0.7000 UNION
SELECT '1964-06-01', 0.7000 UNION
SELECT '1964-09-01', 1.3900 UNION
SELECT '1964-12-01', 0.6800 UNION
SELECT '1965-03-01', 0.6800 UNION
SELECT '1965-06-01', 1.3500 UNION
SELECT '1965-09-01', 0.6700 UNION
SELECT '1965-12-01', 1.3200


I am trying to return the following results:

Qtr CPI YrCPI AvgYrCPI
-------- ----- ----- --------
1-Jun-60 1.48
1-Sep-60 1.46
1-Dec-60 0.72
1-Mar-61 0.71 4.37
1-Jun-61 0.71 3.60
1-Sep-61 -0.70 1.44
1-Dec-61 0.00 0.72 2.53
1-Mar-62 0.00 0.01 1.44
1-Jun-62 0.00 -0.70 0.37
1-Sep-62 0.00 0.00 0.01
1-Dec-62 0.00 0.00 -0.17
1-Mar-63 0.00 0.00 -0.18
1-Jun-63 0.00 0.00 0.00
1-Sep-63 0.71 0.71 0.18
1-Dec-63 0.00 0.71 0.36
1-Mar-64 0.70 1.41 0.71
1-Jun-64 0.70 2.11 1.24
1-Sep-64 1.39 2.79 1.76
1-Dec-64 0.68 3.47 2.45
1-Mar-65 0.68 3.45 2.96
1-Jun-65 1.35 4.10 3.45
1-Sep-65 0.67 3.38 3.60
1-Dec-65 1.32 4.02 3.74

Note, 4 data points are required to calculate a moving sum of CPI
(YrCPI) and 4 calculate YrCPI figures are required calculate the
annual average of YrCPI (AvgYrCPI), giving a staggered effect to the
first 7 results

This sad effort is about as far as I've got:

SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPI
FROM QtrInflation I
JOIN (
SELECT TOP 4 Qtr, CPI
FROM QtrInflation
) S
ON S.Qtr <= I.Qtr
GROUP BY I.Qtr, I.CPI
ORDER BY I.Qtr ASC

Can anyone suggest how do achieve this result without having to resort
to cursors?

Thanks,

Stephen