title: sql query where difference of latest date in current year and current date is in range on join
body: <p>I have two tables. vehicle and service dates.</p>
  <pre><code>vehicle
  id | vin | type | no_of_wheels | chassis_number |
  ------------------------------------------------
   1 | QWE | Car  |      4       | AIZI2Q
   2 | ASD | Bike |      2       | AIQQWA
   3 | ZXC | Car  |      4       | AIQQIWA
   4 | JKL | Bike |      2       | AZZZZZZ
  
  service_dates 
  id | vin | last_service_date
  -----------------------------
  1  | QWE | '2020-11-15'
  2  | ASD | '2020-10-10'
  3  | QWE | '2021-01-10'
  4  | ASD | '2020-11-15'
  5  | ASD | '2021-01-10'
  6  | ZXC | '2020-12-12'
  7  | QWE | '2021-01-20'
  8  | ASD | '2021-01-20'
  9  | JKL | '2021-03-01'
  </code></pre>
  <p>I want to generate a query where I want to list all the vehicles whose service is pending. The logic is that the most recent last_service_date is more than 30 days prior to today(&quot;2021-03-04&quot;) and only the last_service_date in the current year need to be considered, if there is no last_service_date in current year for that vehicle, then that vehicle automatically qualifies for service.</p>
  <p>so the output should be:-</p>
  <pre><code>vehicle
  id | vin | type | no_of_wheels | chassis_number |
  ------------------------------------------------
   1 | QWE | Car  |      4       | AIZI2Q
   2 | ASD | Bike |      2       | AIQQWA
   3 | ZXC | Car  |      4       | AIQQIWA
  </code></pre>
  <p>Explanation:- since the most recent last_service_date for QWE and ASD is more than 30 days before today they are listed. and since ZXC is not done this year, it also qualifies. But JKL was done just 4 days ago so that is not listed.</p>
  <p>what I have so far:-</p>
  <pre><code>Select v.* , max(sd.last_service_date) 
  from vehicle v Left JOIN service_dates sd on sd.vin = v.vin
  and EXTRACT(YEAR FROM CAST(sd.sd.last_service_date AS DATE)) = EXTRACT(YEAR FROM now())
  and ((extract(day from now() - CAST(sd.last_service_date AS DATE)) - 30) &gt; 0))
  group by v.vehicle_guid;
  </code></pre>
  <p>this won't work as it doesn't account for vehicles with no service date in current year, and the max(sd.last_service_date) is for selecting but it does not select the most recent service date.
  Any help is much appreciated.</p>