aggregation over two hierarchies

From: navrsale <navrsalemile_at_yahoo.ca>
Date: 22 Feb 2005 13:00:01 -0800
Message-ID: <1109106001.953120.321290@c13g2000cwb.googlegroups.com>

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, 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, 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 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 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).

thanks,
mile Received on Tue Feb 22 2005 - 15:00:01 CST