CODEWITHSUNDEEP
sqlsql-server-2012self-joincumulative-sum
Elelwani Mbadaliga
Elelwani Mbadaliga

Reputation: 23

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

Upvotes: 1

Views: 144

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460158

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.

Upvotes: 1

Related Questions