CODEWITHSUNDEEP
sqlcount
Kanupriya Dhiman
Kanupriya Dhiman

Reputation: 1

Counting employees from one job level to another

I have a snapshot of a dataset as follows:

effective_date  hire_date   name    job_level   direct_report
01.01.2018      01.01.2018  xyz        5            null
01.02.2018      01.01.2018  xyz        5            null
01.03.2018      01.01.2018  xyz        5            null
01.04.2018      01.01.2018  xyz        6            null
01.05.2018      01.01.2018  xyz        6            null
01.01.2018      01.02.2018  abc        5            null
01.02.2018      01.02.2018  abc        5            null
01.03.2018      01.02.2018  abc        5            null
01.04.2018      01.02.2018  abc        5            null
01.05.2018      01.02.2018  abc        5            null

I want to find out as to how many employees moved/promoted from level 5 to level 6 during this overall time?

Upvotes: 0

Views: 110

Answers (2)

Serkan Arslan
Serkan Arslan

Reputation: 13393

you can try this.

select count(distinct name) from employees e1
WHERE effective_date between '01.01.2018' and '01.05.2018'
And job_level = 5
and EXISTS (select * from employees e2 where e1.name = e2.name
           and e2.effective_date > e1.effective_date
            and e2.job_level = 6
           )

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Here is one method that uses two levels of aggregation. You can get the employees that were promoted by comparing the minimum date for "5" to the maximum date of "6":

select name
from t
where job_level in (5, 6)
group by name
having min(case where job_level = 5 then effective_date end) < max(case where job_level = 6 then effective_date end);

To count them:

select count(*)
from (select name
      from t
      where job_level in (5, 6)
      group by name
      having min(case where job_level = 5 then effective_date end) < max(case where job_level = 6 then effective_date end)
     ) x;

Alternatively, you can use lag():

select count(distinct name)
from (select t.*, lag(job_level) over (partition by name order by effective_date) as prev_job_level
      from t
     ) t
where prev_job_level = 5 and job_level = 6;

The two are subtly different, but within the range of the ambiguity of the question. For instance, the first would count 5 --> 4 --> 6, the second would not.

Upvotes: 1

Related Questions