T-SQL Problem with Mileages and Dates 2

[JGALEY]

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

 

[pascalsql]

Try this:

select 
   VB.vehicle_id, 
   min(VB.date) beg_date,
   VB.odometer beg_odo,
   VE.date end_date,
   VE.odometer end_odo
from vehicle_history VH
join vehicle_history VE on VE.vehicle_id = VB.vehicle_id
     and VE.date = (select max(date) from vehicle_history V3
                    where V3.vehicle_id = VB.vehicle_id)
group by VB.vehicle_id, VB.odometer, 
         VE.date, VE.odometer

Note: I can't check the above syntax (no sql at home)
and I'm not satisfied with it.
Any one with better idea ? (subquery)

 

[JGALEY]

thanks for the help: i am defintely on the right track. i was getting 19000 rows for one vehicle, so i decided to eliminate the ending odomter readings to simplify the joins. now i get 'only' 618 rows for one vehicle.

here is the code:

select et.etehuid as ehuid,
et.ettype as m_type,
etb.etdateposted as beg_date,
etb.etreading as beg_meter
from etodaymeter et join etodaymeter etb on et.etehuid = etb.etehuid
where etb.etdateposted = (select min(etdateposted) from
etodaymeter where etehuid = et.etehuid and
etdateposted between 20030501 and 20030531) and et.etehuid = 365

(for simplicity, i added the single veh id of 365 and one month date range: these will later be replaced or removed.)

so, it looks like a join problem here. any ideas? thanks!

jason

 

[JamesLean]

Have a go with this. I made a temp table to test with:

CREATE TABLE #mileage (
  VehicleID int,
  Date int,
  Mileage int
)

I then inserted some test data and I think this query should give you what you want. The two variables are just so you can specify you only want to see records between two dates:

DECLARE @start int,
  @end int

SELECT @start = '20030601', @end = '20030630'

SELECT
  a.VehicleID,
  a.date AS BeginDate,
  b.date AS EndDate,
  a.mileage AS BeginMileage,
  b.mileage AS EndMileage
FROM
  (
  SELECT vehicleid, date, mileage
  FROM #mileage m1
  WHERE date = (SELECT MIN(date) FROM #mileage WHERE vehicleid = m1.vehicleid AND date BETWEEN @start AND @end)
  ) a
    JOIN
  (
  SELECT vehicleid, date, mileage
  FROM #mileage m1
  WHERE date = (SELECT MAX(date) FROM #mileage WHERE vehicleid = m1.vehicleid AND date BETWEEN @start AND @end)
  ) b
    ON a.vehicleid = b.vehicleid
ORDER BY a.vehicleid

--James

 

[JGALEY]

works GREAT, thanks!