i need help with a t-sql challenge. i have a table with mileages and dates for vehicles set up like this:
column 1: vehicle ID
column 2: date (integer format YYYYMMDD)
column 3: odometer reading
there are rows for each vehicle for each date and mileage entered, and these rows make up an odometer history for each vehicle.
what i need to do is take each vehicle's entries over a period of time, get the lowest date and the highest date in that range, and then get the corresponding odometer reading for the lowest and highest date. finally, i want to assemble these readings into one row for each vehicle in a new table set up like this:
column 1: vehicle id
column 2: beginning date
column 3: ending date
column 4: beginning odometer
column 5: ending odometer
what is the best way to do this?
thanks in advance,
jason
column 1: vehicle ID
column 2: date (integer format YYYYMMDD)
column 3: odometer reading
there are rows for each vehicle for each date and mileage entered, and these rows make up an odometer history for each vehicle.
what i need to do is take each vehicle's entries over a period of time, get the lowest date and the highest date in that range, and then get the corresponding odometer reading for the lowest and highest date. finally, i want to assemble these readings into one row for each vehicle in a new table set up like this:
column 1: vehicle id
column 2: beginning date
column 3: ending date
column 4: beginning odometer
column 5: ending odometer
what is the best way to do this?
thanks in advance,
jason