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'
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'
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' )
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' )
ASKER
hi slightwv
sorry, forgot to also mention that the needed result of US_ID should be the latest incident of START_DATE.
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
/
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select distinct US_ID from (
select US_ID from table_name
minus
select US_ID from table_name where UPDATE_TYPE = 'CS'
)