All Forums Database
shoaibrafiq 5 posts Joined 04/15
17 Sep 2015
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

 

dnoeth 4628 posts Joined 11/04
17 Sep 2015
select
   case Status
      when 3 then 'Approved'
      when 4 then 'Declined'
      else        'In Progress'
   end,
   count(*)
...
group by 1

 

Dieter

saravanatn 10 posts Joined 07/11
17 Sep 2015

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

shoaibrafiq 5 posts Joined 04/15
17 Sep 2015

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

 

shoaibrafiq 5 posts Joined 04/15
17 Sep 2015

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

 

 

dnoeth 4628 posts Joined 11/04
17 Sep 2015

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

 

Dieter

You must sign in to leave a comment.