Running Sum Query

create table #t(OrderDate smalldatetime, ProductKey int, QtyOrdered int)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/1/07',1,1)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/1/07',1,1)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/1/07',1,1)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/2/07',1,1)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/3/07',1,1)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/4/07',1,1)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/5/07',1,5)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/6/07',1,6)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/7/07',1,7)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/8/07',1,8)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/9/07',1,7)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/10/07',1,6)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/11/07',1,1)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/12/07',1,1)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/10/07',2,1)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/11/07',2,1)
insert into #t(OrderDate,ProductKey,QtyOrdered) values ('7/12/07',2,1)

--Group to the OrderDate,ProductKey level
select 
t.OrderDate,
t.ProductKey,
QtyOrdered = sum(t.QtyOrdered)
into #Agg1
from #t t 
group by t.OrderDate,t.ProductKey

--Review the orders...
select * from #Agg1 order by ProductKey,OrderDate;

--Get running sum
;WITH S AS (
SELECT r.OrderDate,r.ProductKey,r.grp,m.mgrp 
FROM (
SELECT
ProductKey,
count(distinct OrderDate) mgrp
FROM
#Agg1
GROUP BY ProductKey
) m
JOIN (
SELECT
OrderDate,
ProductKey,
ROW_NUMBER() OVER (PARTITION BY ProductKey ORDER BY OrderDate) grp
FROM
#Agg1
) r
on r.ProductKey = m.ProductKey
UNION ALL
SELECT
S.OrderDate,
S.ProductKey,
grp = S.grp + 1,
mgrp = S.mgrp
FROM S
WHERE S.grp < S.mgrp
)
select 
OrderDate = MAX(S.OrderDate),
ProductKey = MAX(S.ProductKey),
MTD=sum(a.QtyOrdered)
into #Agg2
from S
join #Agg1 a
on a.OrderDate = S.OrderDate
and a.ProductKey = S.ProductKey
GROUP BY S.ProductKey,S.grp
order by S.ProductKey,Max(S.OrderDate)

--Review running sum
select * from #Agg2 

--Join back to #Agg1 to get the QtyOrdered
select a1.OrderDate, a1.ProductKey,a1.QtyOrdered, a2.MTD from #Agg1 a1
join #Agg2 a2
on a1.OrderDate = a2.OrderDate
and a1.ProductKey = a2.ProductKey
go
drop table #t
drop table #Agg1
drop table #Agg2