32 lines
1.4 KiB
SQL
32 lines
1.4 KiB
SQL
update Log set VehiclePreviousLogId = null;
|
|
|
|
WITH summary AS (
|
|
SELECT
|
|
ROW_NUMBER() OVER (
|
|
PARTITION BY l.LogId
|
|
ORDER BY pl.EndOdometer DESC, pl.[Date] DESC, pl.Created DESC) AS rk,
|
|
l.LogId, pl.LogId as PreviousLogId
|
|
FROM
|
|
[Log] as l,
|
|
[Log] as pl
|
|
WHERE
|
|
pl.VehicleId = l.VehicleId
|
|
AND ((pl.EndOdometer < l.EndOdometer
|
|
AND pl.[Date] <= l.[Date])
|
|
OR (
|
|
pl.EndOdometer = l.EndOdometer
|
|
AND pl.[Date] < l.[Date]
|
|
)
|
|
OR (
|
|
pl.EndOdometer = l.EndOdometer
|
|
AND pl.[Date] = l.[Date]
|
|
AND pl.Created < l.Created
|
|
))
|
|
)
|
|
UPDATE [Log]
|
|
SET [Log].VehiclePreviousLogId = summary.PreviousLogId
|
|
FROM summary
|
|
WHERE summary.rk = 1
|
|
AND [Log].LogId = summary.LogId
|
|
|