Distinct values based on correlated MIN() and MAX() values

I have a table called EVENTS and I need to write 4 scripts to generate 4 separate tables of distinct EVENT_ID values. Below is the first script and the other 3 will be of a similar logic. Can anyone help with this first script please so I can then hopefully use it as a template for the other 3 scripts. I am writing these scripts in SQL2005 which must be backwards compatible with SQL2000. I have removed any duplicates so there shouldn't be a need to involve the rank of the EVENT_ID in the logic.

For each CARE_ID select the value of EVENT_ID which has an EVENT_TYPE of CP and has a MAX(EVENT_DATE) which is <= the MIN(EVENT_DATE) where the EVENT_TYPE is in ('B','CH','S', 'T')

    CARE_ID    EVENT_ID     EVENT_DATE  EVENT_TYPE
    3          194          01/10/2012  S
    3          228          07/07/2010  S
    3          104          12/05/2010  CH
    3          16           12/07/2010  B
    3          17           13/07/2010  B
    3          43           15/01/2010  P
    3          189          15/04/2010  S
    39         45           09/10/2009  T
    39         4            21/07/2009  P
    39         6            21/07/2009  CH
    78         28           08/07/2009  S
    78         706          08/12/2010  CP
    78         707          09/12/2010  CP
    78         9            28/07/2009  T
    78         11           28/07/2009  CH
    95         21           31/07/2009  CH
    95         21           31/07/2009  T
    107        1474         21/09/2012  S
    107        93           23/02/2010  CP
    107        59           29/10/2012  P
    107        58           29/12/2009  P
    151        186          19/03/2010  S
    151        49           21/03/2010  T
    152        69           26/08/2009  CH
    206        85           21/08/2009  CP
    206        84           28/07/2009  CP
    217        158          18/02/2010  S
    217        102          30/03/2010  CH
    218        159          12/03/2010  S
    227        1378         01/04/2011  CP
    355        19           13/07/2010  B
    355        20           13/07/2010  B
    355        239          13/07/2010  S
    355        56           16/07/2010  T
    355        111          16/07/2010  CH
    364        1136         18/02/2011  CP
    364        569          19/02/2011  S
    364        774          23/08/2012  CH
    364        1122         26/01/2011  CP
    367        247          01/07/2010  S
    367        151          21/06/2010  CP
    369        108          26/07/2010  P
    369        152          27/07/2010  CP
    369        109          28/07/2010  P
    369        117          28/07/2010  CH
    369        248          28/07/2010  S
    380        277          08/07/2011  T
    396        1573         06/06/2011  CP
    481        63           07/09/2010  T
    481        116          07/09/2010  P
    481        194          07/09/2010  CP
    481        289          07/09/2010  S
    502        200          13/08/2010  CP
    530        220          14/06/2010  CP
    535        222          05/07/2010  CP
    535        303          13/07/2010  S
    535        223          19/07/2010  CP
    535        224          26/07/2010  CP
    536        135          10/09/2010  CH
    536        225          23/08/2010  CP
    568        155          06/10/2010  P
    568        315          15/10/2010  S
    631        148          02/02/2010  CH
    631        74           15/01/2010  T
    631        256          15/12/2009  CP
    631        345          15/12/2009  S
    631        147          25/12/2009  CH
    632        259          18/09/2010  CP
    653        189          29/10/2010  P
    653        360          30/09/2010  S
    655        1570         06/06/2011  CP
    680        569          08/12/2010  CP
    680        1191         24/11/2011  S
    680        530          25/01/2011  S
    680        151          30/09/2010  P
    680        281          30/09/2010  CP
    680        480          30/11/2010  CP
    689        306          02/11/2010  CP
    689        158          06/10/2010  P
    689        372          06/10/2010  S
    689        2720         06/11/2012  CP
    689        2736         11/11/2012  CP
    689        2752         13/11/2012  CP
    689        2765         15/11/2012  CP
    689        2125         22/09/2011  CP
    689        2654         24/09/2012  CP
    689        1944         26/08/2011  CP
    689        307          26/10/2010  CP
    689        1947         27/08/2011  CP
    729        299          15/09/2010  CP
    811        413          27/10/2010  S
    834        622          01/01/2012  CH
    834        1233         06/01/2012  S
    834        624          15/01/2012  CH
    834        625          23/01/2012  CH
    834        627          23/01/2012  CH
    838        629          02/01/2012  CH
    838        630          20/01/2012  CH
    838        632          27/01/2012  CH
    846        416          05/10/2010  S
    849        195          03/11/2010  P
    849        336          21/02/2011  CP
    923        441          26/07/2010  S
    963        371          29/10/2010  CP
    981        624          23/03/2011  S
    984        384          13/11/2010  CP
    984        392          18/11/2010  CP

Answers


Tried using a HAVING clause? Maybe I've messed something up, but your test data only seems to have one such entry, namely for the S case, as follows:

SELECT  e.[CARE_ID], e.[EVENT_ID]
FROM    dbo.EVENTS e
WHERE   e.[EVENT_TYPE] = 'CP'
GROUP BY e.[CARE_ID], e.[EVENT_ID]
HAVING  MAX( e.[EVENT_DATE] ) <= ( SELECT  MIN( [EVENT_DATE] )
                                   FROM    [EVENTS]
                                   WHERE   [EVENT_ID] = e.[EVENT_ID]
                                       AND [EVENT_TYPE] = 'S' );

Here's an SQL Fiddle to help sort you out!

Edit: The original fiddle is looking for the maximum date of the EVENT_ID, when we're supposed to be looking for the maximum date of the CARE_ID. I think this will get you on the right track!

SELECT  e.[CARE_ID], e.[EVENT_ID]
FROM    dbo.EVENTS e
WHERE   e.[EVENT_TYPE] = 'CP'
GROUP BY e.[CARE_ID], e.[EVENT_ID]
HAVING  MAX( e.[EVENT_DATE] ) <= ( SELECT  MIN( [EVENT_DATE] )
                                   FROM    [EVENTS]
                                   WHERE   [CARE_ID] = e.[CARE_ID]
                                       AND [EVENT_TYPE] = 'S' );

Edit 3: Now with proper DATETIME!

Edit 4: Distinct EVENT_ID!

SELECT DISTINCT e.[EVENT_ID]
FROM dbo.EVENTS e
WHERE e.[EVENT_TYPE] = 'CP'
GROUP BY e.[CARE_ID], e.[EVENT_ID]
HAVING MAX( e.[EVENT_DATE] ) <= ( SELECT MIN( [EVENT_DATE] )
                                 FROM dbo.[EVENTS]
                                 WHERE [CARE_ID] = e.[CARE_ID]
                                     AND [EVENT_TYPE] = 'S' );

This translate quite directly into SQL using window functions:

select care_id, event_id
from (select e.*,
             max(case when event_type = 'CP' then event_date end) over (partition by care_id) as MaxED_CP,
             min(case when event_type = ('B','CH','S', 'T') then event_date end) over (partition by care_id) as MinED_others,
      from events e
     ) e
where event_type = 'CP' and
      MaxED_CP <= MinED_others and
      event_date = MaxED_CP;

These functions calculate the max() and min() values on every row. The outer query just selects the appropriate rows.

Note that if no such event exists for a given care_id then the care_id is not in the output.

This is not backwards compatible with SQL Server 2000. If it works, then you can replace the window functions with subqueries.


Need Your Help

Python - __iter__ method return instance.__str__ and instance.name ?

python oop class iterator

I am learning OOP python using the Markana tutorial and I'm having some trouble with Lab 16.8. (Among some other troubles) I can't get the __iter__ method correct. My method looks like this:

C# Monotouch/Xamarin.iOS - Scrollable UIView

c# ios uiview xamarin.ios scrollable

If I have a UIView whose height is larger than the height of the UIScreen, and I fill it with other objects such as textbox's and label's. How do I make it so I can scroll through that entire view ...