I have a table containing a series of names, events and dates. I've created a new field 'evt5_date' which is related to a specific event (evt5).
Each name can have several events the timing of each is recorded in evt_date field.
Two events evt1 and evt2 are related to evt5.
I want to insert the date of the first occurrence of an evt5 into all evt1 and evt2 rows preceding the evt5. If there is no evt5 after the evt1 or evt2 then the field is left empty.
All this must be done for each name. There are a few thousand different names. I've only show 2 in the data below
Current table data - no values in evt5_date
name evt_date event evt5_date
name-1 2010-06-30 evt1
name-1 2009-10-30 evt5
name-1 2009-09-30 evt2
name-1 2009-06-30 evt5
name-1 2009-03-30 evt5
name-1 2009-02-28 evt2
name-1 2009-01-30 evt1
name-2 2005-05-30 evt2
name-2 2005-03-30 evt5
name-2 2005-01-30 evt1
How I'd like it to look - values in evt5_date field
name evt_date event evt5_date
name-1 2010-06-30 evt1
name-1 2009-10-30 evt5
name-1 2009-09-30 evt2 2009-10-30
name-1 2009-06-30 evt5
name-1 2009-03-30 evt5
name-1 2009-02-28 evt2 2009-03-30
name-1 2009-01-30 evt1 2009-03-30
name-2 2005-05-30 evt2
name-2 2005-03-30 evt5
name-2 2005-01-30 evt1 2005-03-31
I attempted to perform the update with the code below, but I didn't know how to specify the linkage between the date of evt5 being greater than the evt_date of evt1 and evt2 while also grouping by the