Anonymous February 2016

Multiple conditions in SQL

I have following table:

Employee ID Employee Status Date of Termination 1 A NULL 2 A NULL 3 I 1/1/2016 4 I 12/15/2016 5 I 1/1/2016

enter image description here

I would like to report on the following:

  • Number of current active employees - 2
  • Number of inactive employees - 3
  • Number of employees terminated in last one month - 2

This is the piece of code I used:

select 
case when employee_status='A' then count(employee_id) else '' end, 
case when employee_status='I' then count(employee_id) else '' end, 
case when employee_status='I' 
 then  
 (select count(employee_id) 
  from employee 
 where date_of_termination between '1/1/2016' and '2/1/2016') 
 else '' end 
 from employee

My result set is:

Active | Inactive | Inactive_last_month
     2 |        0 |                   0
     0 |        3 |                   2

I would like to achieve the following:

Active | Inactive | Inactive_last_month
     2 |        3 |                   2

Any recommendations will be appreciated.

Answers


X.L.Ant February 2016

This should be possible to simplify it using SUM :

select 
sum(case when employee_status='A' then 1 else 0 end) as active, 
sum(case when employee_status='I' then 1 else 0 end) as inactive, 
sum(case when employee_status='I' and date_of_termination between '1/1/2016' and '2/1/2016' then 1 else 0 end) as inactive_last_month
 from employee


Kamran Farzami February 2016

I woudl wrap the case satements in sum() function and also modify the ELSE part of the CASE Statement to be 0.

so it will look like something like this:

select 
SUM(case when employee_status='A' then count(employee_id) else 0 end) AS Active, 
SUM(case when employee_status='I' then count(employee_id) else 0 end) AS Inactive, 
SUM(case when employee_status='I' 
then  
 (select count(employee_id) 
  from employee 
 where date_of_termination between '1/1/2016' and '2/1/2016') 
 else 0 end) AS Inactive_last_month
 from employee


Tom H February 2016

You need to SUM up the number of rows that match each criteria:

SELECT
    SUM(CASE WHEN date_of_termination IS NULL THEN 1 ELSE 0 END) AS active,
    SUM(CASE WHEN date_of_termination IS NOT NULL THEN 1 ELSE 0 END) AS inactive,
    SUM(CASE WHEN date_of_termination BETWEEN '20160101' AND '20160201' THEN 1 ELSE 0 END) AS inactive_last_month
FROM
    Employee

I've ignored the employee_status column with the assumption that the date is sufficient to determine whether or not the employee is active/inactive - in which case that column probably shouldn't even exist in the table since it's duplicating data.

Post Status

Asked in February 2016
Viewed 3,026 times
Voted 5
Answered 3 times

Search




Leave an answer