hr@XE:11g2> column deptjob format a15;
hr@XE:11g2> column ename format a22;
hr@XE:11g2> column dept format 9999;
hr@XE:11g2> column deptjob format a15;
hr@XE:11g2> column ename format a18;
hr@XE:11g2> column sprank format 999.9;
hr@XE:11g2> column tprank format 999.9;
hr@XE:11g2> column skew format 999.9;
hr@XE:11g2> column adjustment format $99999.99;
hr@XE:11g2> column dept_cum format $99999.99;
hr@XE:11g2> r
1 WITH
2 skewed_pay AS
3 (
4 SELECT
5 department_id, job_id,
6 department_id||job_id AS deptjob,
7 first_name||' '||last_name AS ename,
8 salary,
9 100*PERCENT_RANK() OVER
10 (PARTITION BY department_id||job_id ORDER BY salary ASC) AS sprank,
11 sysdate - hire_date AS tenure,
12 100*PERCENT_RANK() OVER
13 (PARTITION BY department_id||job_id ORDER BY hire_date DESC) AS tprank
14 FROM employees
15 WHERE department_id||job_id IN
16 (SELECT deptjob FROM
17 (SELECT COUNT(*), department_id||job_id AS deptjob
18 FROM employees
19 GROUP BY department_id||job_id HAVING COUNT(*) > 3 )
20 )
21 ),
22 adjust_calc AS
23 (
24 SELECT
25 department_id,
26 job_id,
27 ename,
28 salary,
29 tenure,
30 tprank - sprank AS skew,
31 (CASE
32 WHEN tprank-sprank >= 25.0 AND tprank-sprank < 50.0 THEN .01*salary
33 WHEN tprank-sprank >= 50.0 AND tprank-sprank < 75.0 THEN .02*salary
34 WHEN tprank-sprank >= 75.0 THEN .025*salary
35 ELSE 0
36 END) adjustment
37 FROM
38 skewed_pay
39 WHERE
40 tprank - sprank >= 25.0
41 )
42 SELECT
43 department_id AS dept, job_id, ename, salary, skew, adjustment,
44 TO_CHAR(100.00*RATIO_TO_REPORT(adjustment) OVER (), '99.99') AS R_to_R,
45 SUM(adjustment) OVER (PARTITION BY department_id ORDER BY department_id ASC
46 ROWS UNBOUNDED PRECEDING) AS dept_cum
47 FROM adjust_calc
48 ORDER BY 1 ASC, 4 DESC;
Dept. JOB_ID ENAME SALARY SKEW ADJUSTMENT R_TO_R DEPT_CUM
~~~~~ ~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~ ~~~~~~~ ~~~~~~ ~~~~~~~~~~ ~~~~~~ ~~~~~~~~~~
30 PU_CLERK Sigal Tobias 27776 25.0 $277.76 2.79 $277.76
50 ST_MAN Payam Kaufling 78368 50.0 $1567.36 15.73 $1567.36
50 ST_CLERK James Marlow 24800 52.6 $496.00 4.98 $2063.36
60 IT_PROG Valli Pataballa 76186 25.0 $761.86 7.65 $761.86
60 IT_PROG David Austin 76186 75.0 $1904.64 19.12 $2666.50
60 IT_PROG Diana Lorentz 66662 25.0 $666.62 6.69 $3333.12
80 SA_REP Patrick Sully 94240 28.6 $942.40 9.46 $942.40
80 SA_REP Allan McEwen 89280 28.6 $892.80 8.96 $1835.20
80 SA_REP Lindsey Smith 79360 39.3 $793.60 7.96 $2628.80
80 SA_REP Louise Doran 74400 25.0 $744.00 7.47 $3372.80
100 FI_ACCOUNT Ismael Sciarra 91661 25.0 $916.61 9.20 $916.61
11 rows selected.