pietlinden,

Aha, Itzik Ben-Gan calls this 'sliding' and 'cumulative'

Thanks for looking that up. Your query is for 'sliding aggregate'. I turned to pg 453 and copied the following pattern which appears to work for 'cumulative aggregate'.

SELECT o1.username,

o1.move_in_date AS toMonth,

o1.Cnt_Lead_id AS CntThisMonth,

CAST(AVG(1. * o2.Cnt_Lead_id) AS NUMERIC (4,0)) AS '3MonthRollingAvgCnt'

FROM dbo.countHistory o1

JOIN dbo.countHistory o2

ON o2.username = o1.username

AND (o2.move_in_date > DATEADD(month, -3, o1.move_in_date)

AND o2.move_in_date <= o1.move_in_date)

GROUP BY o1.username, o1.move_in_date, o1.Cnt_Lead_id

ORDER BY o1.username, o1.move_in_date;

IBG says "the main difference between the solution for cumulative aggregates and the solution for sliding aggregates is in the join condition (or in the subquery's filter in the case of the alternate solution using subqueries)....."I am trying to understand still, but it does appear I have a query that will now further me along in my goal today. Thanks much.