SQL - find most recent promotion date

Question

Say I have a table "EmployeePromotions" with data similar to:

Job_Title      Job_Description      Name          Effective_Date
DM1            Manager              John Doe      12/01/2016
AM1            Manager              John Doe      01/12/2015
ASM            Assist Manager       John Doe      10/01/2014
MG1            Manager              John Doe      07/15/2014       
ASM            Assist Manager       John Doe      03/01/2012
CSV            Service Rep          John Doe      011/21/2010

I want to be able to query and return the minimum effective date with a Job_Description of "Manager" without any gaps in Job_Description. I know this is confusing so for example:

For John Doe, I want to return this record only:

Job_Title      Job_Description      Name          Effective_Date
AM1            Manager              John Doe      01/12/2015

The reason I wouldn't want the first occurrence of "Manager" with date of 07/15/2014 is because he got demoted and then promoted once more on 01/12/2015. I only want the most recent promotion date with no gaps in Job_Description = "Manager".

The Job_Description of Manager has many different Job_Titles attached to it that are not in any particular hierarchy so it's tough to predict the most recent grouping based on the job title.

ok. So grade hierarchy wise `Manager` > `Assist Manager` > `Service Rep`. Is that a correct assumption? — RBT, May 04 '17 at 00:29
what do you need as output when a Manager gets demoted and never promoted again? — Vamsi Prabhala, May 04 '17 at 01:21

Vamsi Prabhala · Accepted Answer · 2017-05-04T02:00:26.687

select Job_Title,Job_Description,Name,Effective_Date 
from (select t.*
      ,min(case when job_description = 'Manager' then grp end) over(partition by name) as min_mgr_grp
      ,min(effective_date) over(partition by name,grp) as start_date
      from (select t.*
            ,row_number() over(partition by name order by effective_date desc) 
            - row_number() over(partition by name order by job_description,effective_date desc) as grp
            from t
            ) t
     ) t
where job_description = 'Manager' and grp = min_mgr_grp and effective_date = start_Date

Classify consecutive rows with the same job_description into one group for a given name using a difference of row numbers approach. (inner-most query)
Then get the minimum group number (groups assigned based on descending order of effective_date) for job_description = Manager and also the minimum effective_date (start_date for that group). (second inner-most query)
Finally select the row with minimum group and the start_date per above.

Sample Demo

Clear, concise and explains exactly what it does. Thank you! — Jorjo Peralta, May 04 '17 at 01:55

SQL - find most recent promotion date

1 Answers1