CODEWITHSUNDEEP
sqlsql-server
Anthbs
Anthbs

Reputation: 184

SQL best way to subtract a value of the previous row in the query?

I'm trying to calculate the total lost machine time in the database. The columns in the table are MachineID aka PlantID, StartTime, EndTime.

In theory its simply sort the table by machineID and by StartTime then take the StartTime of the current row and subtract the previous rows EndTime.

Here is my current query that works:

SELECT SUM([StartTime] - [EndTime]) AS TotalLostTime  
FROM(SELECT * 
     FROM (SELECT ROW_NUMBER() OVER (ORDER BY [PlantID], [StartTime]) AS [Row],
                  [MachineRecords].PlantID , [MachineRecords].EndTime 
           FROM [MachineRecords]) AS a 
     JOIN (SELECT * 
           FROM (SELECT ROW_NUMBER() OVER (ORDER BY [PlantID], 
                                                        [StartTime]) AS [Row1],
                        [MachineRecords].PlantID as PlantID1 , 
                        [MachineRecords].StartTime 
                 FROM [MachineRecords]) as b) m 
     ON m.PlantID1 = a.[PlantID] 
        AND a.[Row] = m.[Row1]-1) lostTimeQuery

My question is: Is there a better(more consise) way of achieving the same result of this query?

Thanks for the help.

EDIT:

After the comment from wildplasser i've created this query:

SELECT SUM(a.StartTime - a.LagEnd) as LostTime 
FROM (SELECT [PlantID], [StartTime], [EndTime], 
             LAG([PlantID]) OVER (ORDER BY PlantID, StartTime) LagPlantID,
             LAG([EndTime]) OVER (ORDER BY PlantID, StartTime) LagEnd 
      FROM MachineRecords) a 
WHERE a.PlantID = a.LagPlantID

Upvotes: 1

Views: 8293

Answers (1)

Anthbs
Anthbs

Reputation: 184

This was the resulting query I went with :)

SELECT SUM(a.StartTime - a.LagEnd) as LostTime 
FROM (SELECT [PlantID], [StartTime], [EndTime], 
             LAG([PlantID]) OVER (ORDER BY PlantID, StartTime) LagPlantID,
             LAG([EndTime]) OVER (ORDER BY PlantID, StartTime) LagEnd 
      FROM MachineRecords) a 
WHERE a.PlantID = a.LagPlantID

Upvotes: 1

Related Questions