CODEWITHSUNDEEP
mysql
vvr02
vvr02

Reputation: 611

MySQL: How to get the Active members by month in year

I have got the previous year working members and subtracted previous year relieving employees, then got the previous month relieving list and subtracted it from the result set. Then added the newly added members in a current month.

SQL Fiddle Link I am sensing that there lot of improvements we can do to the current query. But right now I am out of ideas, Can someone kindly help on this?

Upvotes: 2

Views: 357

Answers (2)

cdaiga
cdaiga

Reputation: 4939

I think what you need to do is to get all the employees who are already working from the employee table with:

SELECT * FROM employee WHERE hired_date<= CURRENT_DATE;

Then get the list of employees whose relieving date is still in the future using:

SELECT * FROM employee_separation WHERE relieving_date > CURRENT_DATE;

Then join the two results and group by the month and year of the reliving date as shown below:

SELECT DATE_FORMAT(B.relieving_date, "%Y-%M") RELIEVING_DATE, COUNT(*)   
NUMBER_OF_ACTIVE_MEMBERS FROM 
(SELECT * FROM employee WHERE hired_date <= CURRENT_DATE) A INNER JOIN 
(SELECT * FROM employee_separation WHERE relieving_date > CURRENT_DATE) B 
ON A.emp_id=B.emp_id
GROUP BY DATE_FORMAT(B.relieving_date , "%Y-%M");

Here is a Demo on sql fiddle.

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35593

IF I have interpreted your existing query correctly, I suggest the following:

select
      mnth.num, count(*)
from (
    select 1 AS num union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all 
    select 7 union all select 8 union all select 9 union all select 10 union all select 11 union all select 12
    ) mnth
left join (    
      select
            e.emp_id
          , case 
               when e.hired_date < date_format(current_date(), '%Y-01-01') then 1
               else month(e.hired_date)
               end AS start_month
          , case 
               when es.relieving_date < date_format(current_date(), '%Y-01-01') then 0
               when es.relieving_date >= date_format(current_date(), '%Y-01-01') then month(es.relieving_date)
               else month(current_date())
               end AS end_month
      from employee e
      left join employee_separation es on e.emp_id = es.emp_id
      ) emp on mnth.num between emp.start_month and emp.end_month  
where mnth.num <= month(current_date())
group by
      mnth.num
;

This produced the following result (current_date() on Nov 21 2017

| num | count(*) |
|-----|----------|
|   1 |        6 |
|   2 |        7 |
|   3 |        8 |
|   4 |        9 |
|   5 |       10 |
|   6 |        9 |
|   7 |       10 |
|   8 |       11 |
|   9 |       12 |
|  10 |       13 |
|  11 |       14 |

DEMO

Depending on data volumes adding a where clause in the emp subquery may help, this also affect a case expression:

          , case 
               when es.relieving_date >= date_format(current_date(), '%Y-01-01') then month(es.relieving_date)
               else month(current_date())
               end AS end_month
      from employee e
      left join employee_separation es on e.emp_id = es.emp_id
      where es.relieving_date >= date_format(current_date(), '%Y-01-01')

Upvotes: 2

Related Questions