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 Go to next message
Amine
Messages: 359
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
)
;
The problem with the proposed query is that it doesn't take into account the three consecutive years after the last progression. I'm calculating the running average, and I think this is not the solution.

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 #685627 is a reply to message #685620] Sun, 20 February 2022 03:13 Go to previous messageGo to next message
piripicchio
Messages: 18
Registered: April 2018
Location: Rome
Junior Member
Hi,
if my understanding is right, you need to slide the window so that the lower bound is set to the previous computation of the progression date, which suggests a procedural approach Smile
Re: Let's promote them all ! [message #685628 is a reply to message #685620] Sun, 20 February 2022 06:59 Go to previous messageGo to next message
Amine
Messages: 359
Registered: March 2010
Senior Member

Thanks for the reply piripicchio.
Your understanding is right.

But if I come here, then the purpose is not to do procedural. The purpose is to use pure SQL to optimize the process.
I am not processing one employee. I am processing more then one actually Smile
Re: Let's promote them all ! [message #685629 is a reply to message #685628] Sun, 20 February 2022 08:52 Go to previous messageGo to next message
piripicchio
Messages: 18
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 #685631 is a reply to message #685629] Mon, 21 February 2022 09:05 Go to previous messageGo to next message
Amine
Messages: 359
Registered: March 2010
Senior Member

Thanks for the reply and for the effort you did.
I'am sure 100% that Gurus (@Michel Cadot, @Salomon Yakobson, ...) can do it in SQL.
Re: Let's promote them all ! [message #685633 is a reply to message #685631] Mon, 21 February 2022 12:49 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3152
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.
Re: Let's promote them all ! [message #685636 is a reply to message #685631] Tue, 22 February 2022 01:56 Go to previous messageGo to next message
piripicchio
Messages: 18
Registered: April 2018
Location: Rome
Junior Member
I forgot to attach the script to my last reply Laughing
  • Attachment: DEMO.sql
    (Size: 2.10KB, Downloaded 177 times)

[Updated on: Tue, 22 February 2022 01:56]

Report message to a moderator

Re: Let's promote them all ! [message #685638 is a reply to message #685633] Tue, 22 February 2022 08:25 Go to previous messageGo to next message
Amine
Messages: 359
Registered: March 2010
Senior Member

How did we get 01/04/2006 ?

The last progression was on 01/10/2003. So the next progression will be calculated three (03) years after the last progression e.g in 2006.
So we take the average evaluations of 2003, 2004 and 2005, that is 9,4166. So the next progression will be after 30 months after that last one. That is 01/04/2006.
Re: Let's promote them all ! [message #685701 is a reply to message #685620] Wed, 09 March 2022 15:01 Go to previous message
Amine
Messages: 359
Registered: March 2010
Senior Member

Hi Gurus.

No new post on the SQL & PL/SQL forum since February 22nd.

What's the problem ?
Previous Topic: Help Needed in Date Variable
Next Topic: Oracle calculate hours between timestamps
Goto Forum:
  


Current Time: Mon Jul 04 05:17:13 CDT 2022