aggregation over two hierarchies

[navrsalemile]

In my application there are two given tables:

JOB table:

job_id open closed splitfrom mergedto
=================================================
AAAA 1/1/90 1/1/00
BBBB 1/1/80 1/1/00
CCCC 1/1/00 1/1/01 AAAA EEEE
DDDD 1/1/00 1/1/01 BBBB EEEE
EEEE 1/1/01
FFFF 1/1/01 CCCC

create table job (
job_id char(4) NOT NULL,
open_dte date not null,
close_dte date,
split_from char(4),
merge_to char(4),
PRIMARY KEY (job_id),
FOREIGN KEY (split_from) REFERENCES job(job_id),
FOREIGN KEY (merge_to) REFERENCES job(job_id)
)
/
INSERT INTO job VALUES( 'AAAA', to_date('01/01/90', 'MM/DD/RR'),
to_date('01/01/00', 'MM/DD/YY'), null, null )
/
INSERT INTO job VALUES( 'BBBB', to_date('01/01/80', 'MM/DD/RR'),
to_date('01/01/00', 'MM/DD/YY'), null, null )
/
INSERT INTO job VALUES( 'CCCC', to_date('01/01/00', 'MM/DD/RR'),
to_date('01/01/01', 'MM/DD/YY'), 'AAAA', 'EEEE' )
/
INSERT INTO job VALUES( 'DDDD', to_date('01/01/00', 'MM/DD/RR'),
to_date('01/01/01', 'MM/DD/YY'), 'BBBB', 'EEEE' )
/
INSERT INTO job VALUES( 'EEEE', to_date('01/01/01', 'MM/DD/RR'), null, null,
null )
/
INSERT INTO job VALUES( 'FFFF', to_date('01/01/01', 'MM/DD/RR'), null, 'CCCC',
null )
/
commit
/


and JOB_REPORT table:

job_id job_name YR1 YR2 YR3 YR4 YR5
======================================================
AAAA name1 6 5 4 3 2
BBBB name2 5 4 3 2 1
CCCC name3 3 2 1 2 3
DDDD name4 7 6 5 4 3
EEEE name5 10 9 8 7 6
FFFF name6 3 4 5 6 7


create table job_report (
job_id char(4) NOT NULL,
job_name char(5) not null,
yr1 integer,
yr2 integer,
yr3 integer,
yr4 integer,
yr5 integer
PRIMARY KEY (job_id)
)
/

INSERT INTO job_report VALUES( 'AAAA', 'name1', 6,5,4,3,2 )
/
INSERT INTO job_report VALUES( 'BBBB', 'name2', 5,4,3,2,1 )
/
INSERT INTO job_report VALUES( 'CCCC', 'name3', 3,2,1,2,3 )
/
INSERT INTO job_report VALUES( 'DDDD', 'name4', 7,6,5,4,3 )
/
INSERT INTO job_report VALUES( 'EEEE', 'name5', 10,9,8,7,6 )
/
INSERT INTO job_report VALUES( 'FFFF', 'name6', 3,4,5,6,7 )
/
commit
/


JOB table describes different jobs and their history (some jobs are split into another job and some jobs are merged into a new job). For example this hierarchical query describes history inside JOB table:

column change_history format a50
column participant format a12
set pagesize 66
accept p_job_id prompt 'enter job id, 0 for all: '

select distinct DEPTH,
CHANGE_HISTORY,
substr( change_history, instr( change_history, '/', -1, 1 ) + 1 ) PARTICIPANT,
decode( DEPTH - 1, 0, null, EVENT ) EVENT
from (
select level DEPTH,
sys_connect_by_path( job_id, '/' ) CHANGE_HISTORY,
'SPLIT' EVENT
from job
start with DECODE( '&p_job_id', '0', '1', job_id ) = decode( '&p_job_id', '0', '1', '&p_job_id')
connect by prior split_from = job_id
union all
select level DEPTH,
sys_connect_by_path( job_id, '/' ) CHANGE_HISTORY,
'MERGE' EVENT
from job
start with DECODE( '&p_job_id', '0', '1', job_id ) = decode( '&p_job_id', '0', '1', '&p_job_id' )
connect by prior job_id = merge_to
)
order by 2;

which gives this result:

DEPTH CHANGE_HISTORY PARTICIPANT EVENT
--------- ----------------------- ------------ -----
1 /AAAA AAAA
1 /BBBB BBBB
1 /CCCC CCCC
2 /CCCC/AAAA AAAA SPLIT
1 /DDDD DDDD
2 /DDDD/BBBB BBBB SPLIT
1 /EEEE EEEE
2 /EEEE/CCCC CCCC MERGE
2 /EEEE/DDDD DDDD MERGE
1 /FFFF FFFF
2 /FFFF/CCCC CCCC SPLIT
3 /FFFF/CCCC/AAAA AAAA SPLIT


So here is the problem: a query must be created which combines above "history query" with table JOB_REPORT to produce following result:

job_id job_name YR1 ....
==========================================
AAAA name1 6
BBBB name2 5
CCCC name3 9 (*
DDDD name4 12 (**
EEEE name5 31 (***
FFFF name6 12 (****

if the query is for all JOB_IDs

or just

job_id job_name YR1 ....
==========================================
EEEE name5 31

if the query is for specific JOB_ID (EEEE in this example). Query must show data for every YR (YR1, YR2, YR3, YR4, YR5)

remark (* : 9 is sum of 3 and 6 where 3 is the number of people who worked on job CCCC under that job name only, and where 6 is the number of people who worked on job AAAA from which job CCCC was split. In this example I only showed column YR1 but the same logic is applicable for columns YR2, YR3,YR4 and YR5 as well

remark (**: 12 is the sum of 7 and 5 where 7 is the number of people who worked on job DDDD under that job name only, and where 5 is the number of people who worked on job BBBB from which job DDDD was split.

remark (***: 31 is the sum 10+9+12 where 10 is the number of people who worked on job EEEE under that job name only and 9 is explained in remark (* and 12 is explained in remark (**. I.e. CCCC and DDDD were merged into EEEE

remark (****: 12 is the sum 3+9 where 3 is the number of people who worked on job FFFF only and 9 is explained in remark (*. I.e. FFFF was split from job CCCC.

Basically, in this example data job CCCC was created by splitting from job AAAA, and job DDDD by splitting from job BBBB. CCCC and DDDD were then merged into EEEE, while at the same time job FFFF was split from CCCC. Query must process each row in JOB_REPORT table and aggregate the number of employees when these numbers represent the "history" of particular JOB_ID to correct existing value in JOB_REPORT table.
(to "prorate" the number of employees in JOB_REPORT table based on the history of given JOB_ID represented inside JOB table).