question

ThaiHuynh-0158 avatar image
0 Votes"
ThaiHuynh-0158 asked MelissaMa-msft commented

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

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


sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

While asking a question, you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your code attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·

Hi @ThaiHuynh-0158,

Could you please validate all the answers so far and provide any update?

If all are not working, please provide more sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @ThaiHuynh-0158,

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;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ThaiHuynh-0158,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.