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

I am trying to calculate the total machine time loss time in a database. The columns in the table are MachineID aka PlantID, StartTime, EndTime.

In theory, just sort the table by machineID and StartTime, then take the StartTime of the current line and subtract the previous EndTime lines.

Here is my current request 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 to achieve the same result from this query?

Thanks for the help.

EDIT:

After a comment from wildplasser, I created this request:

 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 
+1
sql sql-server
source share
1 answer

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 
+1
source share

All Articles