วันอังคารที่ 9 ตุลาคม พ.ศ. 2555

Sql server , Summary accumulate , running total



I prefer  the second and the third sulution

Solution 2: The "Celko" Solution

(NoIndex = 25 secs, Index = 20 secs)
SELECT DayCount,
       Sales,
       Sales+COALESCE((SELECT SUM(Sales) 
                      FROM Sales b 
                      WHERE b.DayCount < a.DayCount),0)
                         AS RunningTotal
FROM Sales a
ORDER BY DayCount



Solution 3: The "Guru's Guide" Solution

(NoIndex = 38 secs, Index = 17 secs)
SELECT a.DayCount,
       a.Sales,
       SUM(b.Sales)
FROM Sales a
CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales