Good morning everyone, I am having a little trouble figuring this out
Code:
SELECT eoh.Equipment_EquipmentID, e.AccountingCode, eoh.ReadingDate ,eoh.TrueReading,
(select TOP 1 LAG.TrueReading from Equipment_OdometerHistory as LAG
WHERE (LAG.Equipment_EquipmentID < eoh.Equipment_EquipmentID) or (LAG.Equipment_EquipmentID = eoh.Equipment_EquipmentID and LAG.TrueReading < eoh.TrueReading)
GROUP BY LAG.Equipment_EquipmentID, LAG.TrueReading
ORDER BY LAG.Equipment_EquipmentID DESC, LAG.TrueReading DESC
) AS Prev_Value, (eoh.TrueReading-Prev_Value) AS [Elapsed Odometer]
FROM Equipment AS e INNER JOIN Equipment_OdometerHistory AS eoh ON e.EquipmentID = eoh.Equipment_EquipmentID
WHERE (((e.IsDeleted)=False))
GROUP BY eoh.Equipment_EquipmentID, e.AccountingCode, eoh.ReadingDate, eoh.TrueReading;
The purpose of this query is to take values and lag them so that I can calculate a difference between the two values. It achieves this goal... for example:
The only part that I am having trouble with is making the previous value and elapsed odometer cells null when the EquipmentID changes as underlined in the below image.
Can this be done? Thanks ahead of time for any help!