calculating based on meeting a range condition

I have a database (Faster) that stores detailed information of thousands of vehicles and equipment. My goal is to get a list of vehicles and equipment that have accrued less than 1000 miles on them over the past two years (since 20090101). The three 开发者_StackOverflow中文版main tables I am dealing with are:

eheader (has equip info, primary key=ehuid)

wheader (has work order info; whehuid links to ehuid, 
         work order primary key is whuid), 

wmreading (has the meter reading info linked to each workorder; 
           wmwhuid links to whuid).  

The tricky part is that for each piece of equipment, I need to take the difference between a recent meter reading and a meter reading that took place approximately 2 years ago.

The other tricky part is the approximate. I can't guarantee that a meter reading occurred for each vehicle in say January of 2009 and also January of 2011. I can only assume a work order happened on that piece of equipment within a range of about 3 months (and even that would probably leave some vehicles out).

I need a query that for each vehicle will take a meter reading from a work order in some range of early 2009 and subtract a meter reading for that same vehicle from a work order in some recent range of late 2010 to now.

Any thoughts on how to query this?

This should help you out

select
    *,
    DATEDIFF(D, readingdate, secondreadingdate) as DaysBetweenReadings,
    secondmeter-meter as DistanceTravelled
from
(
select
    e.*,
    r1.readingdate,
    r2.readingdate secondreadingdate,
    r1.meter,
    r2.meter as secondmeter,
    rn= ROW_NUMBER() over (
    partition by e.ehuid
    order by -- prefer the one closer to date
             datediff(d, p.dt, r1.readingdate)
           , -- from the first reading, prefer the r2 record that is closest to exactly 2 years after
             abs(datediff(d, dateadd(year,2,r1.readingdate), r2.readingdate)))
from eheader e
inner join wheader w on w.whehuid = e.ehuid
inner join wmreading r1 on r1.wmwhuid = w.whuid
inner join wmreading r2 on r2.wmwhuid = w.whuid
cross join (select dt=convert(datetime,'20090101')) p -- pivotdate
where -- about 3 months from date
      r1.readingdate between p.dt and DATEADD(MONTH, 3, p.dt) 
  and -- 3 months to or from 2 years after date
      r2.readingdate between DATEADD(month, 21, p.dt) and DATEADD(month, 27, p.dt) 
) SubQuery
where rn=1
  and secondmeter-meter < 1000 -- less than 1000 accrued
order by ehuid