What script would return the required final table?

Question

In the following table what script would return one row per CARE_ID which has an EVENT_TYPE of CP which is before the first instance of TR. Else return the most recent instance of CP. The priority comparator is EVENT_DATE and the tie breaker is MAX(EVENT_ID) Below is the expected final table.

CARE_ID     EVENT_ID    EVENT_DATE          EVENT_TYPE
3           117         09/04/2010 00:00    CP
3           104         11/04/2010 00:00    TR
3           190         16/04/2010 00:00    TR
3           16          12/07/2010 00:00    TR
3           17          13/07/2010 00:00    TR
3           18          13/07/2010 00:00    TR
78          11          27/07/2009 00:00    TR
78          9           28/07/2009 00:00    TR
78          706         08/12/2010 00:00    CP
78          707         09/12/2010 00:00    CP
107         93          23/02/2010 00:00    CP
107         1474        21/09/2012 00:00    TR
206         84          28/07/2009 00:00    CP
206         85          21/08/2009 00:00    CP


CARE_ID     EVENT_ID    EVENT_DATE          EVENT_TYPE
3           117         09/04/2010 00:00    CP
78          707         09/12/2010 00:00    CP
107         93          23/02/2010 00:00    CP
206         85          21/08/2009 00:00    CP

Answer 1

This type of query should be possible. The following gets the maximum event date, subject to your conditions:

select t.care_id,
       coalesce(max(case when event_date < tr.mined then event_date end),
                max(event_date)
               ) as thedate
from table t left outer join
     (select care_id, min(event_date) mined
      from table t
      where event_type = 'TR'
      group by care_id
     ) tr
     on tr.care_id = t.care_id
where t.event_type = 'CP'
group by t.care_id;

You can then join back to the original table to get the rest of the information:

select t.*
from table t join
     (select t.care_id,
             coalesce(max(case when event_date < tr.mined then event_date end),
                      max(event_date)
                     ) as thedate
      from table t left outer join
           (select care_id, min(event_date) mined
            from table t
            where event_type = 'TR'
            group by care_id
           ) tr
           on tr.care_id = t.care_id
      where t.event_type = 'CP'
      group by t.care_id
     ) tt
     on tt.care_id = t.care_id and tt.thedate = t.event_date and tt.event_type = 'CP';