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;
Nice triangular join. As the number of records increase, this will bring your server to its knees. In fact, at that point, even a cursor-based solution will run faster.