10 Replies Latest reply on Oct 13, 2017 7:32 AM by Michael Simons

    Find MAX Date Before Event

    Michael Simons

      Hi all, would love some urgent help if anyone can offer it!

       

      I’m sure this is a very easy solution but I can’t unpick it. I have two data sources which are blended by car registration number.

       

      I have a list of cars and the date on which they had a crash.

       

      I have a list of cars and the various dates that they had a service.

       

      How can I find the date of the last service each car had before its crash.

       

      Note that most of the cars have had a service since their crash so I can’t just use MAX of crash data.

       

      Thanks!

       

      SERVICE DATA:

       

              

      CarService
      Car 101/01/2017
      Car 106/01/2017
      Car 111/01/2017
      Car 116/01/2017
      Car 221/01/2017
      Car 226/01/2017
      Car 231/01/2017
      Car 305/02/2017
      Car 310/02/2017
      Car 315/02/2017
      Car 4

      20/02/2017

      CRASH DATA

       

              

      CarCrash
      Car 107/01/2017
      Car 122/01/2017
      Car 228/01/2017
      Car 306/02/2017
      Car 313/02/2017
      Car 405/05/2017