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
Really want to see a high performance method? Read this and the discussion following it: