CODEWITHSUNDEEP
sqlsql-server
Ice
Ice

Reputation: 459

sql for Active and Inactive status

I wrote a sql to get the below result:

  Case No: 1          Status:Inactive
    Case No: 2          Status:Active
    Case No: 2          Status:Inactive
    Case No: 3          Status:Active

The sql is :

select Case No, 
Case when status in ( 'Hospital', 'Discharge') then 'Inactive' else 'Active' end as status
from Cases

If for the case no, for example case no 2, there are diffrent status : Inactive and Active, then status is inactive. How can I modify the Sql to get the following result?

Case No :1              Status:Inactive
Case No :2              Status:Inactive
Case No: 3              Status:Active

Upvotes: 1

Views: 15663

Answers (2)

DhruvJoshi
DhruvJoshi

Reputation: 17126

Try below query

Select [Case No],
CASE MAX(Status) WHEN 0 THEN 'active' ELSE 'Inactive' END Status 
FROM
(
 select [Case No], 
   CASE WHEN  status in ( 'Hospital', 'Discharge') THEN 1 ELSE 0 END status
  from Cases ) C
group by [Case No]

SQL demo link

See screenshot enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Given the names you have for your statuses, it seems to be sufficient to use max():

select CaseNo, 
       max(Case when status in ( 'Hospital', 'Discharge') then 'Inactive' else 'Active'
           end) as status
from Cases
group by CaseNo;

A more general solution that doesn't depend on the lexicographical ordering of the names is:

select CaseNo, 
       (case when sum(status in ('Hospital', 'Discharge')) > 0
             then 'Inactive'
        else 'Active'
           end) as status
from Cases
group by CaseNo;

Upvotes: 2

Related Questions