Using top N Employees

Question

I need a query to get Top N employees working on every project in a specific month based on working hours. And I need the same query for all time (without specifying a month).

I get two hints first Hint: Use Substitution variables to prompt the user to enter values for N and a month. Second Hint: Use the rank analytical function. If two employees tie they should get the same rank.

Just right now i have this not completed solution, and not sure if i should complete it:

    SELECT BSR_PROJ.PROJECT_NAME,
       BSR_TM.FNAME || ' ' || BSR_TM.MNAME || ' ' || BSR_TM.LNAME EMPLOYEE_NAME,
       BSR_TRD.WORK_ITEM_DATE,
       RANK() OVER (PARTITION BY BSR_PROJ.PROJECT_NAME ORDER BY BSR_TRD.WORK_ITEM_DATE ASC) EMPRANK
  FROM BSR_TEAM_REPORT_DETAILS BSR_TRD,
       BSR_PROJECTS BSR_PROJ,
       BSR_TEAM_MEMBERS BSR_TM
 WHERE BSR_TRD.BSR_TEAM_RES_ID = BSR_TM.ID
       AND BSR_TRD.BSR_PRJ_ID = BSR_PROJ.ID
       ;

Answer 1

You need to include the month in the analytic function, as rank is calculated per project per month; truncating the date with a month format mask is an easy way to achieve this.

You also need to include that truncated month in the projection, so you can filter on it. I have chosen to present the month in the format 2015-12. You may wish to show it differently.

The query you have will generate the whole set of ranks for all employees across all assignments. You need an outer query to apply the filtering requirements. My solution uses SQL Plus substitution variables rather than bind variables, but the principle is the same:

select distinct project_name
       , employee_name 
from (
    SELECT BSR_PROJ.PROJECT_NAME,
           BSR_TM.FNAME || ' ' || BSR_TM.MNAME || ' ' || BSR_TM.LNAME EMPLOYEE_NAME,
           to_char(trunc(bsr_trd.work_item_date, 'MM'), 'yyyy-mm') as project_month,
           RANK() OVER (PARTITION BY BSR_PROJ.PROJECT_NAME,
                                   trunc(bsr_trd.work_item_date, 'MM') 
                        ORDER BY BSR_TRD.WORK_ITEM_DATE ASC) EMPRANK
      FROM BSR_TEAM_REPORT_DETAILS BSR_TRD,
           BSR_PROJECTS BSR_PROJ,
           BSR_TEAM_MEMBERS BSR_TM
     WHERE BSR_TRD.BSR_TEAM_RES_ID = BSR_TM.ID
           AND BSR_TRD.BSR_PRJ_ID = BSR_PROJ.ID
      )
where project_month = '&proj_month'
and emprank <= &rnk;