Home » SQL & PL/SQL » SQL & PL/SQL » Let's promote them all ! (11.2.0.3)
| Let's promote them all ! [message #685620] |
Fri, 18 February 2022 15:21  |
Amine
Messages: 363
Registered: March 2010
|
Senior Member |
|
|
Hi gurus !
I want to expose complicated process here in our HR department.
It's complicated due of it's complicated business rules.
The purpose of this post is to automate it.
So, we're going to talk about the progression of employees in a company.
I will divide the problem in two (02) big parts :
I. expose the basic problem of progression
II. expose another process that has an impact on progression
First part :
Every year, each employee of the company is evaluated over 10. He will progress, according to the average evaluations of the three consecutive years.
If the average >= 0 and <= 4 then he will progress in 42 months (3.5 years).
If the average > 4 and <= 6 then he will progress in 36 months (3 years).
If the average > 6 and <= 10 then he will progress in 30 months (2.5 years).
For example, an employee has started to work on 01/10/2001. In 2001, he won't be evaluated. Here is the consecutive evaluations until 2010 with the average of each three consecutive years .
ID_EMP E_YEAR EVAL
---------- ---------- ----------
1 2002 8
1 2003 9,5
1 2004 9,5
1 2005 9,25
1 2006 9
1 2007 9,25
1 2008 9
1 2009 9,25
1 2010 9,5
1 2011 9,75
1 2012 8,6
1 2013 8,75
1 2014 9,4
1 2015 9,4
1 2016 8
1 2017 8,7
1 2018 9,1
1 2019 9,9
So in the year 2004, we have the three (03) consecutive years : 2002,2003 and 2004. The average evaluation is 9, so the employee will progress in 30 months after his last progression. As it is his first progression, so he will progress 30 months after his starting date, so after 01/10/2001. So the first progression is 01/04/2003. So the progression is set in 2004 with a retroactive date.
Then, we look to the three consecutive evaluations after his last progression, so after 01/04/2003 : 2003, 2004, 2005. The average evaluation is 9.416, so the employee will progress also in 30 months after his last progression (01/04/2003), that is : 01/10/2005.
and so on.
Another business rule is about the period of interruptions that may occur during the career of an employee. So when en employee is interrupted due to some reasons (lay-off for example) this period is added to the calculated date of progression.
so for example, after the first progression (01/04/2003), we calculate the number of days of interruption during the years 2003, 2004 and 2005 and we add it to 01/10/2005.
So here is a start
alter session set nls_date_format='dd/mm/yyyy';
drop table emp;
create table emp
(
id_emp number ,
dat_start_job date
);
insert into emp values (1, to_date('01/04/2001', 'dd/mm/yyyy'));
drop table emp_eval;
create table emp_eval
(
id_emp number ,
e_year number ,
eval number
);
insert into emp_eval values (1, 2002, 8);
insert into emp_eval values (1, 2003,9.50);
insert into emp_eval values (1, 2004, 9.50);
insert into emp_eval values (1, 2005, 9.25);
insert into emp_eval values (1, 2006, 9.00);
insert into emp_eval values (1, 2007, 9.25);
insert into emp_eval values (1, 2008, 9.00);
insert into emp_eval values (1, 2009, 9.25);
insert into emp_eval values (1, 2010, 9.50);
insert into emp_eval values (1, 2011, 9.75);
insert into emp_eval values (1, 2012, 8.60);
insert into emp_eval values (1, 2013, 8.75);
insert into emp_eval values (1, 2014, 9.40);
insert into emp_eval values (1, 2015, 9.40);
insert into emp_eval values (1, 2016, 8.00);
insert into emp_eval values (1, 2017, 8.70);
insert into emp_eval values (1, 2018, 9.10);
insert into emp_eval values (1, 2019, 9.90);
drop table emp_interrupt;
create table emp_interrupt
(
id_emp number ,
e_year number ,
days number
);
select *
from
(
with v00 as
(
select
emp_eval.id_emp
, emp_eval.e_year
, eval
, dat_start_job
, days days_interrupt
from emp_eval, emp, emp_interrupt
where 1 = 1
and emp_eval.id_emp = emp.id_emp
and emp_eval.id_emp = emp_interrupt.id_emp(+)
order by emp_eval.e_year
), v01 as
(
select id_emp, e_year, eval, dat_start_job
, days_interrupt
, row_number() over (partition by id_emp order by e_year) rn
, avg(eval) over (partition by id_emp order by e_year rows between 2 preceding and current row) avg_3_years
from v00
where 1 = 1
), v02 as
(
select v01.*
, case
when avg_3_years >= 0 and avg_3_years <= 4 then 42 -- 3.5 years
when avg_3_years > 4 and avg_3_years <= 6 then 36 -- 3 years
when avg_3_years > 6 and avg_3_years <= 10 then 30 -- 2.5 years
end nb_months_av
from v01
), v03 as
(
select v02.id_emp, e_year, eval
, dat_start_job
, case
when rn in (1,2) then null
else avg_3_years
end avg_3_years
, case
when rn in (1,2) then null
else nb_months_av
end nb_months_av
, days_interrupt
, add_months(dat_start_job, sum(nb_months_av) over (partition by id_emp order by e_year))
+ nvl(days_interrupt, 0)
dat_prog
from v02
)
select v03.*
from v03
)
;
Any help ?
Thanks Gurus
ID_EMP E_YEAR EVAL DAT_START_ AVG_3_YEARS NB_MONTHS_AV DAYS_INTERRUPT DAT_PROG DAT_PROG_CORRECT
---------- ---------- ---------- ---------- ----------- ------------ -------------- ---------- ----------------
1 2002 8 01/04/2001 01/10/2003
1 2003 9,5 01/04/2001 01/04/2006
1 2004 9,5 01/04/2001 9 30 01/10/2008 01/10/2003
1 2005 9,25 01/04/2001 9,41666667 30 01/04/2011 01/04/2006
1 2006 9 01/04/2001 9,25 30 01/10/2013
1 2007 9,25 01/04/2001 9,16666667 30 01/04/2016
1 2008 9 01/04/2001 9,08333333 30 01/10/2018 01/10/2008
1 2009 9,25 01/04/2001 9,16666667 30 01/04/2021
1 2010 9,5 01/04/2001 9,25 30 01/10/2023 01/04/2011
1 2011 9,75 01/04/2001 9,5 30 01/04/2026
1 2012 8,6 01/04/2001 9,28333333 30 01/10/2028
1 2013 8,75 01/04/2001 9,03333333 30 01/04/2031 01/10/2013
1 2014 9,4 01/04/2001 8,91666667 30 01/10/2033
1 2015 9,4 01/04/2001 9,18333333 30 01/04/2036 01/04/2016
1 2016 8 01/04/2001 8,93333333 30 01/10/2038
1 2017 8,7 01/04/2001 8,7 30 01/04/2041
1 2018 9,1 01/04/2001 8,6 30 01/10/2043 01/10/2018
1 2019 9,9 01/04/2001 9,23333333 30 01/04/2046
|
|
|
|
|
|
|
|
| Re: Let's promote them all ! [message #685629 is a reply to message #685628] |
Sun, 20 February 2022 08:52  |
 |
piripicchio
Messages: 20
Registered: April 2018
Location: Rome
|
Junior Member |
|
|
I'm not 100% sure but I'm pretty confident that you can't do it using only sql or analytic functions (since the window has fixed bounds around current row and it's not what you need).
But, as I said, your requirement it's a perfect fit for a PL/SQL approach so why don't try the easiest e quickest way? With just two types and a function inside a package (because I need the package state to mantain the window) I was able, in about 30 minutes and omitting the emp_interrupt to simplify, to get the results you were looking for:
ID_EMP E_YEAR EVAL DAT_START_ DAT_PROG
---------- ---------- ---------- ---------- ----------
1 2002 8 01/04/2001
1 2003 9,5 01/04/2001
1 2004 9,5 01/04/2001 01/10/2003
1 2005 9,25 01/04/2001 01/04/2006
1 2006 9 01/04/2001
1 2007 9,25 01/04/2001
1 2008 9 01/04/2001 01/10/2008
1 2009 9,25 01/04/2001
1 2010 9,5 01/04/2001 01/04/2011
1 2011 9,75 01/04/2001
1 2012 8,6 01/04/2001
1 2013 8,75 01/04/2001 01/10/2013
1 2014 9,4 01/04/2001
1 2015 9,4 01/04/2001 01/04/2016
1 2016 8 01/04/2001
1 2017 8,7 01/04/2001
1 2018 9,1 01/04/2001 01/10/2018
1 2019 9,9 01/04/2001
|
|
|
|
|
|
| Re: Let's promote them all ! [message #685633 is a reply to message #685631] |
Mon, 21 February 2022 12:49 |
Solomon Yakobson
Messages: 3211
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
I read your post but I am not sure I follow how DAT_PROG_CORRECT is calculated. Your explnations are about first progression dat only which is 30 months from hiring date and is 01/10/2003. We put it for year 2004 retroactively. How did we get 01/04/2006? Why 2006 & 2007 are empty?
SY.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Mar 20 04:59:00 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
