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

Question

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?

What do you mean by: `How to get the Active members by month in year ?` I can't figure out what you are trying to achieve. — cdaiga, Nov 20 '17 at 05:25
@cdaiga we have two table where one table consists of employee and hiring date details and other table consists of employee separation date so I want to get who many of them are active by month. Sorry for my bad english — vvr02, Nov 20 '17 at 05:36
Please provide sample data (for each table) see [Provide a `Minimal Complete Verifiable Example` (MCVE)](https://stackoverflow.com/help/mcve) and [Why should I provide a MCVE](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Paul Maxwell, Nov 20 '17 at 06:07
Well you did add some images that happen to display data. So should I offer a solution query as an image too? Recommended reading: [Provide a `Minimal Complete Verifiable Example` (MCVE)](https://stackoverflow.com/help/mcve) and [Why should I provide a MCVE](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Paul Maxwell, Nov 20 '17 at 20:26
@Used_By_Already thanks for the links updated the question with SQL fiddle link — vvr02, Nov 21 '17 at 03:47

Paul Maxwell · Accepted Answer · 2017-11-21T04:45:59.243

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')

thanks for help as per checking, my query taking less time, I am looking to simplify and improve the performance. — vvr02, Nov 21 '17 at 04:54
Well I had hoped it would improve otherwise I would not have suggested it. What is the speed difference? (facts are more useful that words) did you try it with the where clause, or both? — Paul Maxwell, Nov 21 '17 at 05:00
With small data mine is faster, in real time your query is faster compared to mine. Thank you so much for your valuable time. — vvr02, Nov 21 '17 at 05:10

cdaiga · Answer 2 · 2017-11-21T05:25:50.013

0

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.

edited Nov 21 '17 at 05:25

answered Nov 20 '17 at 07:56

cdaiga

4,861
3
22
42

Thanks for trying to help. No luck with the given query, updated your query to lower case the table names and ran it in sql fiddle it shows no results. 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"); – vvr02 Nov 21 '17 at 03:54
Could you share the link to your sql fiddle so I try? – cdaiga Nov 21 '17 at 04:44
Thank you @vvr02, the thing is that all the relieving date in you employee separation table have already past. So running `SELECT * FROM `employee_separation` WHERE `relieving_date` > CURRENT_DATE;` gives nothing. I considered a user active if `relieving_date > CURRENT_DATE. – cdaiga Nov 21 '17 at 04:58
now also no results are showing up here is the fiddle link with your query along with mine http://sqlfiddle.com/#!9/e1432fa/6 – vvr02 Nov 21 '17 at 05:14
All the relieving dates in your employee_separation table have already past. See my sql fiddle in the demo I just added to my answer. – cdaiga Nov 21 '17 at 05:27
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/159441/discussion-between-vvr02-and-cdaiga). – vvr02 Nov 21 '17 at 05:36

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

2 Answers2