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: 170
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
|
|
|
|
Goto Forum:
Current Time: Thu Jun 20 01:49:05 CDT 2013
Total time taken to generate the page: 0.11522 seconds
|
] 
Members
Search
Help
Register
Login
Home
