May 8, 2008 at 3:49 am
ques
May 8, 2008 at 3:54 am
Romakanta
I'm confused - you say VehicleID is unique, and then you say there are duplicates. Also, the word "consecutive" only has any meaning if you define how the rows are ordered, which you have not done.
John
May 8, 2008 at 6:42 am
OK, hopefully this should get you started. Please post again if there's anything you don't understand.
You can use ROW_NUMBER or RANK (read about both of them and decide which would be better) to put a row number per vehicle ID on the table. Use a CTE (common table expression) for this. Then join the CTE to itself on the same vehicle ID and row number = row number + 1. Once you have that, it will be easy to calculate the difference in mileage and so on.
John
May 8, 2008 at 10:23 am
you should consider adding those two columns to the table and creating a trigger to automatically calculate them. as your data grows, a query that calculates mileage and time on-the-fly could lead to unexpected performance issues.
if you must calculate them on-the-fly, here's a suggestion. i hope TrDt and TrTime are just the date and time portion of a single column (which I'll call TrDate).
; with [your_cte]
as (
select VehID, TrDate, Odometer, TrCity, TrState,
row_number() over (partition by VehId order by TrDt) as sequence
from [your_table]
)
select THIS.*, -- convert TrDate into seperate date and time fields
(THIS.Odometer - PRIOR.Odometer) as Miles,
(THIS.TrDate - PRIOR.TrDate) as TimeDiff -- format it to your liking
from [your_cte] as THIS
left outer join [your_cte] as PRIOR
on THIS.VehId = PRIOR.VehId and THIS.sequence - 1 = PRIOR.sequence
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply