-2

Need SQL query help for the logic as below If at least 1 user under an account is "Enabled" then status = 'Active' If all users under an account are not "Enabled" then status = 'Cancelled' else 'Unknown'

create table usr (
  acctid varchar(1),
  usrid  varchar(5),
  sts  varchar(20)
  );

insert into usr values('A','U13','Enabled');
insert into usr values('A','U14','Enabled');
insert into usr values('A','U15','Disconnected');
insert into usr values('A','U16','Disconnected');
insert into usr values('B','U30','Disconnected');
insert into usr values('B','U31','Disconnected');
insert into usr values('C','U32',null);
insert into usr values('D','U133','Enabled');
insert into usr values('D','U12','Enabled');

Below is the expected result for status

enter image description here

  • 1
    Please give us DDL (CREATE TABLE...) and DML (INSERT INTO...) for the table above - you can do this in a fiddle. Also, please show us explicitly the result you desire. p.s. welcome to the forum! 8-) – Vérace Nov 4 at 23:33
  • 1
    Please check the updated post with DDL – Karthik Rajashekaran Nov 5 at 1:33
1

You need in something like

SELECT *, 
       CASE WHEN COUNT(CASE WHEN sts = 'Enabled' THEN 1 END) OVER (PARTITION BY acctid) > 0 
            THEN 'Active'
            WHEN COUNT(CASE WHEN sts IS NOT NULL THEN 1 END) OVER (PARTITION BY acctid) > 0
            THEN 'Cancelled'
            ELSE 'Unknown'
            END Status
FROM usr

fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.