nick.fairway (4/11/2012)

---

A much more efficient version of this takes about 1/6 of the processing:

SELECT G.OrderDate, G.ProductKey, QtyOrdered = SUM(F.QtyOrdered), G.MTD
FROM
(
SELECT
ProductKey
, OrderDate
, MTD= (SELECT sum(QtyOrdered) FROM #t WHERE OrderDate <= a.OrderDate AND ProductKey = a.ProductKey)
FROM #t A
GROUP BY ProductKey, OrderDate
) G
JOIN #t F
ON G.ProductKey = F.ProductKey AND G.OrderDate = F.OrderDate
GROUP BY G.ProductKey,G.OrderDate, G.MTD
ORDER BY G.ProductKey,G.OrderDate;

nick.fairway (4/11/2012)

---

Even more efficient for the test data supplied is this one - almost 9 times faster than the original -- look at the execution plan of the batch when you run them side by side:


SELECT a.ProductKey,
a.OrderDate,
a.QtyOrdered,
SUM(b.QtyOrdered)
FROM (
SELECT
t.OrderDate,
t.ProductKey,
QtyOrdered = sum(t.QtyOrdered)
FROM #t t
GROUP BY t.OrderDate,t.ProductKey) a
CROSS JOIN #t b
WHERE (b.OrderDate <= a.OrderDate AND a.ProductKey = b.ProductKey)
GROUP BY a.OrderDate, a.QtyOrdered, a.ProductKey
ORDER BY a.ProductKey, a.OrderDate, a.QtyOrdered