We help IT Professionals succeed at work.

Query to select item with substatus

Medium Priority
615 Views
Last Modified: 2012-08-20
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'
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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'
)
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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' )

Author

Commented:
hi slightwv

sorry, forgot to also mention that the needed result of US_ID should be the latest incident of START_DATE.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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
/
Project Architect
CERTIFIED EXPERT
Commented:
try:
SELECT *
FROM (
   SELECT US_ID, start_date,
          row_number() over(partition by US_ID order by start_date DESC) rn, 
          max(case when UPDATE_TYPE = 'CS' then 1 else 0 end) over(partition by US_ID) csid
   FROM table_name )
WHERE rn = 1 and csid = 0

Open in new window

Explore More ContentExplore courses, solutions, and other research materials related to this topic.