Case Statement

Database
Enthusiast

Case Statement

Hi,

I need to write a case statement. I have a table which shows the full history of an applications. An application can have max 5 statuses once or more than once e.g. as below: Status are 1,2,3,4,5. I want to write a case statement which should manage that how many applications have been approved (status = 3), declined (status = 4) and all else (which are neither approved or declined) are 'In Progress'

Appl_Id Status Status Desc
12345678 1 Entered
12345678 2 Analysed
12345678 3 Approved
12345678 5 Booked
98765432 1 Entered
98765432 2 Analysed
98765432 4 Declined
55555555 1 Entered
55555555 2 Analysed
5 REPLIES
Junior Contributor

Re: Case Statement

select
case Status
when 3 then 'Approved'
when 4 then 'Declined'
else 'In Progress'
end,
count(*)
...
group by 1
Enthusiast

Re: Case Statement

Hi,

CREATE TABLE APPLICATION(

Appl_Id INTEGER,

Status INTEGER,

Status_Desc VARCHAR);

INSERT INTO APPLICATION VALUES ('12345678','1','ENTERED');

INSERT INTO APPLICATION VALUES ('12345678','2','ANALYSED');

INSERT INTO APPLICATION VALUES ('12345678','3','APPROVED');

INSERT INTO APPLICATION VALUES ('12345678','5','BOOKED');

INSERT INTO APPLICATION VALUES ('98765432','4','DECLINED');

SELECT APPL_ID,STATUS,

CASE STATUS

 WHEN '3' THEN STATUS_DESC

WHEN '4' THEN STATUS_DESC

ELSE 'INPROGRESS'

END AS STATUS_DESC FROM APPLICATION ORDER BY STATUS

Enthusiast

Re: Case Statement

Hi DNoeth,

I already used the following but it does not work because the table shows the full history of an application. If any application is approved then the following code will show Approved and In progress status for the same application

select

   case Status

      when 3 then 'Approved'

      when 4 then 'Declined'

      else        'In Progress'

   end,

   count(*)

...

group by 1


Enthusiast

Re: Case Statement

If any application is neither approved (3) or nor declined (4) then this app should show under 'in progress'




 

Junior Contributor

Re: Case Statement

Ok, you want a kind of best match per application: Approved -> Declined -> In Progress 

You need nested aggregates:

select
case minStatus
when 1 then 'Approved'
when 2 then 'Declined'
else 'In Progress'
end,
cnt
from
(
select
minStatus, -- count per best match
count(*) as cnt
from
(
select Appl_Id,
min(case Status -- assign a ranking to the Status and get the lowest rank
when 3 then 1 -- 'Approved'
when 4 then 2 -- 'Declined'
else 3
end) as minStatus
from application
group by 1
) as dt
group by 1
) as dt
order by minStatus