Home » SQL & PL/SQL » SQL & PL/SQL » Employee summary (9.2.8.0)
Employee summary [message #575063] Fri, 18 January 2013 13:40
Amine
Messages: 255
Registered: March 2010
Senior Member

Hi all,

Suppose the data I am going to give (Positions and my_job) are data about an employee.
An employee can be in different departments across his life in the company. This is the Position table.

drop table position
/
create table position
(
  id_emp    int     ,
  id_dpt    int     , 
  dat       date      /* the date when he went there */
)
/

alter table position add constraint PK_emp_position primary key (id_emp, dat)
/
insert into position values (1, 1, to_date('1/1/2000'));
insert into position values (1, 2, to_date('1/1/2004'));
insert into position values (1, 3, to_date('1/1/2008'));
insert into position values (1, 4, to_date('1/1/2010'));
insert into position values (1, 5, to_date('1/1/2012'));


The my_job table describes the jobs this employee did a cross his life in the company.

drop table my_job
/

create table my_job
(
  id_emp          int   ,
  job_id          int   ,
  dat_start       date  , /* when he started working the job */
  dat_end         date    /* when he ended the job */
)
/

alter table my_job add constraint PK_emp_job primary key (id_emp, dat_start)
/
-- So for employee 1, we have this jobs history
insert into my_job values (1, 10, to_date('1/2/2000'), to_date('1/3/2000'));

insert into my_job values (1, 20, to_date('1/2/2004'), to_date('1/5/2004'));

insert into my_job values (1, 30, to_date('1/1/2008'), to_date('1/2/2008'));
insert into my_job values (1, 40, to_date('1/3/2008'), to_date('1/3/2009'));


We have then another tables considered as event tables. Each table has its own properties according to business logic.

For simplicity, let consider these four (04) tables :

-- 01 -----
drop table emp_training
/

create table emp_training
(
  id_emp               int   ,
  training_id          int   ,  
  dat_start            date  , /* when he started the training */
  dat_end              date    /* when he ended the training */
)
/

alter table emp_training add constraint PK_emp_training primary key (id_emp, dat_start)
/

insert into emp_training values (1, 10, to_date('2/3/2000'), to_date('15/3/2000'));

-- 02 -----
drop table emp_wedding
/

create table emp_wedding
(
  id_emp               int   ,
  id_pers              int   , /* with whom he was married */ 
  dat_event            date    /* when he was married */
)
/

insert into emp_wedding values (1, 100, to_date('17/3/2000'));

-- 03 -----
drop table emp_childs
/

create table emp_childs
(
  id_emp               int   ,
  id_child             int   , /* Who is this child */  
  dat_event            date    /* when he has got the child */
)
/

insert into emp_childs values (1, 1000, to_date('2/3/2010'));

-- 04 -----
drop table emp_category
/

create table emp_category
(
  id_emp               int   ,
  id_category          int   , /* According to business rules */ 
  dat_move             date  /* when he moves to a new category */
)
/
alter table emp_category add constraint UK_emp_cat unique (id_emp, id_category)
/
insert into emp_category values (1, 5, to_date('15/01/2004'));


About emp_category : An employee during his life in the company moves from a category to another according to some business rules (these rules are not important for my purpose here). But he only goes forward, e.g. moving from a category to another without coming back to an old category.

Some business rules :
1- an employee can not start two jobs at the same time
2- an employee can not have a training (a course) while he is doing a job.

In general, there are 3 types of events with this order of priority:
-1 Driven event (DE) : position.
-2 events with duration in time (TE) : my_job, emp_training.
-3 Punctual events (PE) : emp_category, emp_wedding, emp_childs.

Here is the requirement : we want to synthesize an employee history in the company. We want to have this kind of output :
I want to query these tables to get something like this :

 event    dat_start   dat_end   additional info
--------  ---------   --------  ----------------
in dpt 1  1/1/2000
          1/2/2000    1/3/2000  doing job = 10
          2/3/2000    15/3/2000 doing training = 10
          17/3/2000             married with 100
in dpt 2  1/1/2004              moves to category 5 on 15/01/2004
          1/2/2004    1/5/2004  doing job = 20
in dpt 3  1/1/2008    1/2/2008  doing job = 30
          1/3/2008    1/3/2009  doing job = 40
in dpt 4  1/1/2010
          2/3/2010              has got a new child (1000)
in dpt 5  1/1/2012          


As you can see, the thing that drives us along the employee history is the position table (DE). At a specific department, an employee can get a set of events (PE or TE).

To accomplish this, I thought about something like :

select 'position' event_type, id_emp, dat dat_start, null dat_end,some_function_to_summarize_the_row() additional_info
from position
union all
select 'my_job', id_emp, dat_start, dat_end, some_function_to_summarize_the_row()
from my_job
union all
select 'emp_training', ...
from emp_training
union all
select 'emp_wedding', ...
from emp_wedding


then to consider the event as an object (i.e. defining a new type with an aggregate function like stragg(event)).
Then we could issue something like
select event, dat_start, dat_end, summarize(event)
from 
(
  select 'position', 'my_jobs', ...
  union all
  select 'position', 'emp_training', ...
  union all
  select 'position', 'emp_wedding', ...
  union all
  ...
  order by dat_start

)
group by event
/


Second observation, is that there is meaning less events : like emp_category. This event is meaning less for managers meaning it does not appear as a whole event (a row in the summary). It is just an additional info.

In the case where we have more than one category move :
 
insert into emp_category values (1, 5, to_date('15/01/2004'));
insert into emp_category values (1, 6, to_date('16/01/2004'));
insert into emp_category values (1, 7, to_date('17/01/2004'));

then this line :

in dpt 2  1/1/2004              moves to category 5 on 15/01/2004


becomes

in dpt 2  1/1/2004              moves to category 5 on 15/01/2004, to category 6 on 16/01/2013, 
                                to category 7 on 17/01/2013




Third observation, is about events that occur at the same time. Look at this line :
in dpt 3  1/1/2008    1/2/2008  doing job = 30


It contains a POSITION event as well as a MY_JOB event. We haven't :
in dpt 3  1/1/2008    
          1/1/2008    1/2/2008  doing job = 30


In general, when more then two events occur to an employee, then all events are concatenated in additional info. Suppose we have :

insert into emp_wedding values (1, 100, to_date('2/3/2000'));


instead of

insert into emp_wedding values (1, 100, to_date('17/3/2000'));


then the line :
2/3/2000    15/3/2000 doing training = 10


becomes :

2/3/2000    15/3/2000 doing training = 10, married with 100 on 2/3/2000


if he changes a category on the same time we will have :

2/3/2000    15/3/2000 doing training = 10, married with 100 on 2/3/2000, moved to category x on 10/3/2000


etc etc etc

Here, managers reads this line in that way :
Employee goes to department X on 2/3/2000. At the same time he begun a training until 15/3/2000. In this period, he get married with 100 on 2/3/2000 and moved to category x on 10/3/2000.

Another "Best way" question for you all, but I will ask it differently : I am here to do this in a proper and in an elegant way !
Thanks in advance !

Regards,
Amine
Previous Topic: bulk collect
Next Topic: Query Problem (merged)
Goto Forum:
  


Current Time: Tue Jul 22 09:17:12 CDT 2014

Total time taken to generate the page: 0.27444 seconds