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
       ;

Help us help you... show us table structures, sample input, desired output and whatever code you have thus far — Hambone, Dec 28 '15 at 14:59
Plus: show us what you have already and where your problem lies with the task — jera, Dec 28 '15 at 15:00
@Hambone and jera please vote the question up cause i've modify it as requested and note that i'm new to stackoverflow — mustafa mohammed, Dec 28 '15 at 18:04

score 1 · Accepted Answer · answered Dec 28 '15 at 16:41

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;

Thanks very much sir i'll check it tomorrow and get back to you. — mustafa mohammed, Dec 28 '15 at 18:05
Dear , would you please advice how to re-write your solution to retrieve data based on working hours. Note that working hours is a column exists in BSR_TEAM_REPORT_DETAILS table as WORK_ITEM_CONSUMED_HOURS name. — mustafa mohammed, Dec 29 '15 at 07:54

score 0 · Answer 2 · answered Jan 03 '16 at 09:27

The answer i found as below :

SELECT *
  FROM (SELECT RANK ()
                  OVER (PARTITION BY PROJ_NAME ORDER BY WORKING_HOURS DESC)
                  AS EMPRANK,
               PROJ_NAME,
               EMPLOYEE_NAME,
               WORKING_HOURS
          FROM (  SELECT BSR_PROJ.PROJECT_NAME AS PROJ_NAME,
                            BSR_TM.FNAME
                         || ' '
                         || BSR_TM.MNAME
                         || ' '
                         || BSR_TM.LNAME
                            EMPLOYEE_NAME,
                         SUM (WORK_ITEM_CONSUMED_HOURS) AS WORKING_HOURS
                    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
                         AND TO_CHAR (TRUNC (BSR_TRD.WORK_ITEM_DATE, 'MM'),
                                      'YYYY-MM') = '&PROJ_MONTH'
                GROUP BY BSR_PROJ.PROJECT_NAME,
                            BSR_TM.FNAME
                         || ' '
                         || BSR_TM.MNAME
                         || ' '
                         || BSR_TM.LNAME)) INNER_TABLE
 WHERE EMPRANK <= &RNK;

Using top N Employees

2 Answers2