Determine completion of an ID if that ID has many completion status - some are complete and some open

Question

My sample data fictitiously looks like this:

Incidence_ID, Order_ID, Order_Status

Each incidence will have more than one order_id, and each order_id has one status - either "completed" or "open"

I need to write a query to find out for each incidence_id if it is fully completed where each and all of its order_status is "complete". Anything less, the incidence_id is "open"
In the attached sample data, incidence A is complete and B and C are open.
84596-incidence-status.txt

Answer 1

Hi @Thai Huynh ,

Please try the following solution.

SQL

DECLARE @tbl TABLE (INCIDENCE_ID CHAR(1), ORDER_ID CHAR(2), ORDER_STATUS VARCHAR(20));  
INSERT INTO @tbl (INCIDENCE_ID, ORDER_ID, ORDER_STATUS) VALUES  
('A', 'A1', 'Completed'),  
('A', 'A2', 'Completed'),  
('A', 'A3', 'Completed'),  
('A', 'A4', 'Completed'),  
('A', 'A5', 'Completed'),  
('A', 'A6', 'Completed'),  
('B', 'B1', 'Completed'),  
('B', 'B2', 'Completed'),  
('B', 'B3', 'Completed'),  
('B', 'B4', 'Open'),  
('B', 'B5', 'Completed'),  
('B', 'B6', 'Completed'),  
('C', 'C1', 'Completed'),  
('C', 'C2', 'Open'),  
('C', 'C3', 'Open'),  
('C', 'C4', 'Open'),  
('C', 'C5', 'Open'),  
('C', 'C6', 'Open'),  
('C', 'C7', 'Open');  
  
;WITH rs AS  
(  
SELECT INCIDENCE_ID,  
       SUM(CASE ORDER_STATUS WHEN 'Completed' THEN 1 ELSE 0 END) AS Completed,  
       COUNT(*) as Total  
FROM   @tbl  
GROUP  BY INCIDENCE_ID  
)  
SELECT t.*   
, IIF(rs.Completed = rs. Total, 'complete', 'open') AS FinalStatus  
FROM @tbl as t  INNER JOIN rs ON t.INCIDENCE_ID = rs.INCIDENCE_ID;  

Answer 2

Hi @Thai Huynh ,

Welcome to Microsoft Q&A!

Please also refer below:

select distinct a.INCIDENCE_ID  
, IIF(b.INCIDENCE_ID is null,'Completed','Open')  ORDER_STATUS  
 from @tbl a  
 left join (select INCIDENCE_ID from @tbl where ORDER_STATUS='Open') b  
 on a.INCIDENCE_ID=b.INCIDENCE_ID  

Output:

INCIDENCE_ID	ORDER_STATUS  
A	Completed  
B	Open  
C	Open  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.