• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 605
  • Last Modified:

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'
0
Paul_ATL
Asked:
Paul_ATL
  • 3
1 Solution
 
slightwv (䄆 Netminder) 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'
)
0
 
slightwv (䄆 Netminder) 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' )
0
 
Paul_ATLAuthor Commented:
hi slightwv

sorry, forgot to also mention that the needed result of US_ID should be the latest incident of START_DATE.
0
 
slightwv (䄆 Netminder) 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
/
0
 
lwadwellCommented:
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

0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now