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
sql sql-server
Anthbs
source share