CODEWITHSUNDEEP
sqlsql-server
codeSeven
codeSeven

Reputation: 469

IF ROWS IS ALL EQUAL

I want to use CASE WHEN Command, if my column IsApproved is all equal to 1 then the display is 'COMPLETE' then if there's still 0 then it is pending, depending on the ResignTxn number. How can I do that?

For example, if there's still 0 in txn number 45, then the output must be pending, then if all the value is 1 then it must be complete.

enter image description here

Upvotes: 0

Views: 71

Answers (5)

Serg
Serg

Reputation: 22811

Check min value

select  ResignTxn , case min(IsApproved) when 1 then `COMPLETE'  else 'pending' end
from mytable
group by ResignTxn

Upvotes: 1

Anu
Anu

Reputation: 325

You may use:

select case when IsApproved = 1 then 'Complete' else 'Pending' end as Status
from YOURTABLE

Upvotes: -1

Shikhar Arora
Shikhar Arora

Reputation: 886

SELECT CASE WHEN MIN(IsApproved) =0 THEN 'Pending' ELSE 'Complete' END AS Status
FROM [Table]
Group by ResignTxn

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37480

Try below query:

select ResignTxn,
       -- it counts 0 in particular ResignTxn
       case when sum(case when isApproved = 0 then 1 else 0 end) > 0 then 'pending' else 'complete' end
from MyTable
group by ResignTxn

Upvotes: 3

Rob
Rob

Reputation: 11798

I personally would leave the logic out of the DB, just get the relevant data and evaluate it in your application, but you can do it like this if you want:

SELECT COUNT(*) AS countResult
WHERE IsApproved=0,
CASE
    WHEN countResult > 0 THEN "PENDING"
    ELSE "COMPLETE"
END AS ResultText
FROM myTable;

Upvotes: 0

Related Questions