Hi, I was looking for some help in regards to the following query I have written to give me the ntile for each agent based on the month and job type. Unfortunately for some reason (I have looking through 215 records and I cant find any difference) I have 4 agents that appear within more than 1 quintile i.e. Agent A appears in Quintile and Quintile 2 and there is nothing different in regards to the values so a bit confused.
Unfortunately I am not able to paste the insert queries as I have over 4.5k rows of data but I have managed to paste the create query and also the sql query that I have written to get the output. I know the SQL query could probably be narrowed instead of having so many sub queries but I have managed to get 90% of my task completed bar this issue. If somebody could have a look through my sql code would really appreciate it.
CREATE TABLE "FACT_WEEKLY"
( "B_ID" NUMBER(10,0),
"SOURCE_NAME" VARCHAR2(255 BYTE),
"JOB_TYPE" VARCHAR2(255 BYTE),
"C_ID" NUMBER(25,0),
"OPEN1" VARCHAR2(255 BYTE),
"OPEN_CATEGORY" VARCHAR2(255 BYTE),
"LAST" VARCHAR2(255 BYTE),
"DETAIL_LAST" VARCHAR2(255 BYTE),
"EXPR10" VARCHAR2(255 BYTE),
"EXPR11" VARCHAR2(255 BYTE),
"STATUS" VARCHAR2(255 BYTE),
"TEAM" VARCHAR2(255 BYTE),
"JOB_HANDLER" VARCHAR2(255 BYTE),
"JOB_REF" VARCHAR2(255 BYTE),
"E_DATE" DATE,
"C_DATE" DATE,
"PRODUCT" VARCHAR2(255 BYTE),
"C_NAME" VARCHAR2(255 BYTE),
"PROCESS" VARCHAR2(255 BYTE),
"TOUCHES" NUMBER(25,0),
"MAXOFCLOSED_DATE" DATE,
"REPEAT" NUMBER(25,0),
"CLOSED_WEEKEND" DATE,
"ID1" VARCHAR2(255 BYTE),
"WORKED_HOURS" NUMBER(4,2),
"VOL" NUMBER(25,0),
"CYCLE_TIME" NUMBER(25,0),
"TIME_FACTOR" NUMBER(25,0),
"SITE" VARCHAR2(255 BYTE),
"MANAGER" VARCHAR2(255 BYTE),
"CASE_TYPE2" VARCHAR2(255 BYTE),
"ROW_NO" NUMBER,
"ROW_NO_C" NUMBER,
"HOURS" NUMBER,
"HOURS_C" NUMBER,
"MONTHNAME" VARCHAR2(255 BYTE),
"QUARTER" VARCHAR2(255 BYTE)
)
and the following is my sql query to get the ntile scores.
SELECT
M.*
,NTILE(5) OVER (PARTITION BY MONTH_ENDING, JOB_TYPE ORDER BY M_JPH DESC) AS M_QUINTILE
,DENSE_RANK() OVER (PARTITION BY MONTH_ENDING, JOB_TYPE ORDER BY M_JPH DESC) AS RANK
FROM
(
SELECT
T.*
,DECODE(M_WORKED_HOURS, 0, NULL, ROUND(M_CLOSED / M_WORKED_HOURS,2)) AS M_JPH
FROM
(
SELECT
QUARTER
,MONTHNAME AS MONTH_ENDING
,CLOSED_WEEKEND AS WEEK_ENDING
,JOB_HANDLER
,TEAM AS
,MANAGER
,SITE
,HOURS_C AS WORKED_HOURS
,VOL AS CLOSED
,SUM(VOL) OVER (PARTITION BY MONTHNAME, JOB_HANDLER, JOB_TYPE2) AS M_CLOSED
,SUM(HOURS_ROW_NO_CASE) OVER (PARTITION BY MONTHNAME, JOB_HANDLER, JOB_TYPE2) AS M_WORKED_HOURS
,SOURCE_NAME
,C_ID AS CASE_REF
,JOB_TYPE2 AS JOB_TYPE
,JOB_STATUS
,PRODUCT
,C_NAME
,PROCESS
,E_DATE
,C_DATE
,JOB_REF
,OPEN1
,OPEN_CATEGORY
,RCA_LAST
,RCA_DETAIL_LAST
,EXPR10
,EXPR11
FROM
FACT_WEEKLY
WHERE
JOB_TYPE2 IN ('A', 'B', 'C')
AND
MONTHNAME = 'MAY-15'
ORDER BY
CLOSED_WEEKEND
) T
) M
WHERE
M_JPH IS NOT NULL
AND
JOB_TYPE = 'C'
ORDER BY
DENSE_RANK() OVER (PARTITION BY MONTH_ENDING, CASE_TYPE ORDER BY M_CPH DESC)
;
Like I said, my query could be collapsed to fit within one query rather than the subqueries, so any advice would be really appreciated.
Thanks in advance.