This strictly looks at Diesel Consumption only.
Please note the Previous Speedo calculations are based on the Vehicle's Previous Odometer reading and NOT per date. You can adjust if needed, but I find this the best and most popular way.
SELECT DATE_FORMAT(VtrnDate,'%Y-%m-%d') AS Transaction_Date,
DATE_FORMAT(VtrnInvDate,'%Y-%m-%d') AS Invoice_Date,
DATE_FORMAT(VtrnDate,'%Y-%m') AS YearMonth,
VtrnTT AS Transaction_Code,
Description AS Transaction_Description,
VtrnNr,
OriginFlag,
VtrnFill,
VtrnUserName AS UserName,
VtrnLedger AS Ledger_Code,
VtrnCrNumber AS CrNumber,
VtrnCrName AS CrName,
VtrnVehicleNo AS Vehicle_No,
VtrnRegistration AS Vehicle_Registration,
VtrnDriverCode AS Driver_Code,
VtrnDriverName AS Driver_Name,
VtrnOpenKmManual AS Open_Km_Manual,
VtrnOpeningKms AS Opening_Kms,
VtrnSpeedo AS Vehicle_Odo,
VtrnMapKms AS Map_Kms,
VtrnQuantity AS Quantity,
VtrnPrice AS Price,
VtrnValue AS Cost,
VtrnKmPerLitre AS Vehicle_Consumption,
VtrnSupplierNo AS Supplier_No,
VtrnSubNumber AS SubNumber,
VtrnTruckStop AS Truckstop_Code,
VtrnTruckStopName AS Truckstop_Name,
WEEK(DATE_FORMAT(VtrnDate,'%Y-%m-%d')) AS Transaction_WeekNumber,
YEAR(DATE_FORMAT(VtrnDate,'%Y-%m-%d')) AS Transaction_Year,
MONTH(DATE_FORMAT(VtrnDate,'%Y-%m-%d')) AS Transaction_Month,
LAG(VtrnSpeedo)
OVER (PARTITION BY VtrnVehicleNo
ORDER BY VtrnSpeedo) AS PreviousSpeedo,
(VtrnSpeedo - LAG(VtrnSpeedo)
OVER (PARTITION BY VtrnVehicleNo
ORDER BY VtrnSpeedo)) AS KilometersTraveled,
ROUND((VtrnSpeedo - LAG(VtrnSpeedo)
OVER (PARTITION BY VtrnVehicleNo
ORDER BY VtrnSpeedo)) / VtrnQuantity,
2) AS Kilometers_Per_Litre,
vm.DieselTargetConsumption
FROM vwa_vtrn v
LEFT JOIN vwa_vehiclemaster vm ON v.VtrnVehicleNo = vm.VehicleNumber
WHERE VtrnTT = 'DO'