ChrisM@Work (5/18/2015)
Like this?
SELECTt.*,
x.RollingSum
FROM #team t
OUTER APPLY (
SELECT RollingSum = SUM(expense_amt)
FROM #team ti
WHERE ti.team_id = t.team_id
AND ti.date <= t.date
AND ti.date >= DATEADD(month,-3,t.date)
) x
WHERE t.date >= DATEADD(year, -1, GETDATE())
ORDER BY t.team_id, t.date
I think the OP wants the team totals for 4 quarters, not the 3 month trailing running total at each transaction time for the team. The original post seems fairly clear on that. So maybe something like
WITH quarters(qid, startq, endq) AS (
SELECT dateadd(MM,-12, getdate()), dateadd(MM,-9,getdate()) UNION ALL
SELECT dateadd(MM,-9, getdate()), dateadd(MM,-6,getdate()) UNION ALL
SELECT dateadd(MM,-6, getdate()), dateadd(MM,-3,getdate()) UNION ALL
SELECT dateadd(MM,-3, getdate()) AS startq, getdate() AS endq
),
rev_team(team_id, date, expense_amt) AS (
SELECT t.team_id, q.endq, t.expense_amt
FROM #team t INNER JOIN quarter q ON t.date <= q.endq and t.date > q.startq
SELECT
r.team_id,
x.RollingSum
FROM rev_team r
OUTER APPLY (
SELECT RollingSum = SUM(expense_amt)
FROM rev_team ti
WHERE ti.team_id = r.team_id
AND ti.date = r.date
) x
ORDER BY r.team_id, r.date
(I've been lazy and not tested this, so not guaranteed to be correct.)
Tom