Link to home
Start Free TrialLog in
Avatar of Paul_ATL
Paul_ATL

asked on

Query to select item with substatus

Hello experts,

Need your help to write query please. I have a table that contains thousands of update activities of various items.  The item is defined as US_ID. I want to be able to pull just a unique list of US_ID that have never been updated with 'CS' yupe.

 Here is the sample data

US_ID            START_DATE      UPDATE_TYPE      UPDATE_STATUS
AB0009AAA9      20-Nov-00            MG            CP
AB0009AAA9      13-Feb-02            MG            CP
AB0009AAA9      02-Aug-07            MG            CP
AB0009AAA9      28-Nov-08            CS            CP
FI000M2ED8      23-Nov-01            MD            CP
FI000M2ED8      06-Jan-06            MD            CP
FI00170WD6      30-Mar-01            AD            CP
FI00170WD6      16-Oct-09            CS            CP

The query should only result is the US_ID = FI000M2ED8  because it has never been updated with UPDATE_TYPE = 'CS'
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

You can do this with a not exists query but I like minus:

select distinct US_ID from (
select US_ID from table_name
minus
select US_ID from table_name where UPDATE_TYPE = 'CS'
)
Guess you can try NOT IN:
select distinct US_ID from table_name where US_ID not in
( select US_ID from table_name where UPDATE_TYPE = 'CS' )


NOT EXISTS should be something like:
select distinct US_ID from table_name t1 where not exists
( select null from table_name t2 where t1.US_ID = t2.US_ID and t2.UPDATE_TYPE = 'CS' )
Avatar of Paul_ATL

ASKER

hi slightwv

sorry, forgot to also mention that the needed result of US_ID should be the latest incident of START_DATE.
Then minus won't work.

Just tweak one of the others to add a MAX:


select US_ID, max(start_date) from tab1 where US_ID not in
( select US_ID from tab1 where UPDATE_TYPE = 'CS' )
group by us_id
/


select US_ID, max(start_date) from tab1 t1 where not exists
( select null from tab1 t2 where t1.US_ID = t2.US_ID and t2.UPDATE_TYPE = 'CS' )
group by us_id
/
ASKER CERTIFIED SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial