Elelwani Mbadaliga Elelwani Mbadaliga - 7 months ago 23
SQL Question

Self join causing query to run forever

I'm trying to use a self join to get a cumulative sum. The problem is the query runs for more than 2hrs without any result. How can i fix this.

Select SUM(A.[GrossWeightKg] - A.[QtyLeftKg])/ 1000 AS DailyUsage
, SUM(a.[GrossWeightKg] - a.[QtyLeftKg])/ 1000 AS Cumulative
FROM [PMECentral].[dbo].[FactActualItemUsage] A
LEFT OUTER JOIN [PMECentral].[dbo].[FactActualItemUsage] ff
ON a.ActualItemUsageSourceId >= ff.ActualItemUsageSourceId

Answer

In sql-server 2012 you could use

....
SUM(a.[GrossWeightKg] - a.[QtyLeftKg]) OVER(ORDER BY ActualItemUsageSourceId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
....

without the self join. Look at the OVER-clause especially the part with the Rows-clause.