This discussion is archived
13 Replies Latest reply: Oct 16, 2012 7:54 AM by Pramukh RSS

Effort using Outer join and parent-child relationship

Pramukh Newbie
Currently Being Moderated
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product

I have the following tables

CREATE TABLE  "ADDPROJECT" 
   (     "PROJID" VARCHAR2(30) NOT NULL ENABLE, 
     "PROJNAME" VARCHAR2(30), 
     "PROJSTARTDATE" DATE, 
     "PROJENDDATE" DATE, 
     "PARENTPROJID" VARCHAR2(30), 
     "PROJTYPE" VARCHAR2(30), 
     "PROJSTATUS" VARCHAR2(30), 
      PRIMARY KEY ("PROJID") ENABLE
   )

CREATE TABLE  "ALLOCATEASSOCIATES" 
   (     "PROJID" VARCHAR2(30) NOT NULL ENABLE, 
     "ASSOCIATEID" NUMBER(*,0) NOT NULL ENABLE, 
     "ALLOCATIONSTARTDATE" DATE, 
     "ALLOCATIONPERCENT" NUMBER(*,0), 
     "ENDDATE" DATE, 
      PRIMARY KEY ("PROJID", "ASSOCIATEID") ENABLE
   )

CREATE TABLE  "ADDRESEARCHAREA" 
   (     "RAID" VARCHAR2(30) NOT NULL ENABLE, 
     "RANAME" VARCHAR2(30), 
     "RASTARTDATE" DATE, 
     "RAENDDATE" DATE, 
     "PARENTRAID" VARCHAR2(30), 
     "RASTATUS" VARCHAR2(30), 
      PRIMARY KEY ("RAID") ENABLE
   )

CREATE TABLE  "PROJECTTORAASSOCIATION" 
   (     "RAID" VARCHAR2(30) NOT NULL ENABLE, 
     "PROJID" VARCHAR2(30) NOT NULL ENABLE, 
     "STARTDATE" DATE, 
     "ENDDATE" DATE, 
     "STATUS" VARCHAR2(30), 
      PRIMARY KEY ("RAID", "PROJID") ENABLE
   )

insert into addresearcharea values ('ra1','raname1',to_date('04/01/2012','mm/dd/yyyy'),'','','Active')
insert into addresearcharea values ('ra2','raname2',to_date('04/01/2012','mm/dd/yyyy'),'','','Active')
insert into addresearcharea values ('ra3','raname3',to_date('04/01/2012','mm/dd/yyyy'),'','','Active')
insert into addresearcharea values ('ra4','raname4',to_date('04/01/2012','mm/dd/yyyy'),'','ra1','Active')
insert into addresearcharea values ('ra5','raname5',to_date('04/01/2012','mm/dd/yyyy'),'','ra1','Active')
insert into addresearcharea values ('ra6','raname6',to_date('04/01/2012','mm/dd/yyyy'),'','ra2','Active')

insert into addproject values ('proj1','projname1',to_date('04/01/2012','mm/dd/yyyy'),'','','Research','Active')
insert into addproject values ('proj2','projname2',to_date('04/01/2012','mm/dd/yyyy'),'','','Research','Active')
insert into addproject values ('proj3','projname3',to_date('04/01/2012','mm/dd/yyyy'),'','','Research','Active')

insert into projecttoraassociation values ('ra1','proj1',to_date('04/01/2012','mm/dd/yyyy'),'','Active')
insert into projecttoraassociation values ('ra4','proj1',to_date('04/01/2012','mm/dd/yyyy'),'','Active')
insert into projecttoraassociation values ('ra2','proj2',to_date('04/01/2012','mm/dd/yyyy'),'','Active')
insert into projecttoraassociation values ('ra3','proj3',to_date('04/01/2012','mm/dd/yyyy'),'','Active')

insert into allocateassociates values ('proj1',1,to_date('04/01/2012','mm/dd/yyyy'),100,'')
insert into allocateassociates values ('proj1',2,to_date('04/01/2012','mm/dd/yyyy'),100,'')
insert into allocateassociates values ('proj2',3,to_date('04/01/2012','mm/dd/yyyy'),100,'')
In short here is the data

Research areas : ra1, ra2, ra3
Sub Research areas for ra1 : ra4 , ra5
Sub Research areas for ra2 : ra6
Sub Research areas for ra3 : -

Projects - proj1,proj2.proj3

Projects to Research Area mapping
proj1 : ra1 and ra4
proj2 : ra2

Associate allocation
proj1 : 1,2
proj2 : 3

I need to get the effort research area wise and sub-area displayed along the same row(if the project is associated to the sub research area also)
The user should be able to pick a particular research area and results displayed accordingly
The expected output in case no specific research area is selected
EFFORT PROJECTID PROJECTNAME RANAME SUBRANAME 
12.70 proj1 projname1 raname1 ra4 
6.35 proj2 projname2 raname2 - 
- proj3 projname3 raname3 - 
The expected output in case main research area is selected (eg:- ra1)
EFFORT PROJECTID PROJECTNAME RANAME SUBRANAME 
12.70 proj1 projname1 raname1 ra4 
The expected output in case sub research area is selected (eg:- ra4)
EFFORT PROJECTID PROJECTNAME RANAME SUBRANAME 
12.70 proj1 projname1 raname1 ra4 
The query I have written
SELECT 
to_char(
    SUM(
       (MONTHS_BETWEEN(
               1+LEAST(
                  LEAST(
                     LEAST(
                          nvl(ar.raenddate,to_date('04/01/2100','mm/dd/yyyy'))
                          ,nvl(pr.enddate,to_date('04/01/2100','mm/dd/yyyy')))
                  ,nvl(aa.enddate,to_date('04/01/2100','mm/dd/yyyy')))
              ,sysdate),
              GREATEST(
                 GREATEST(
                    GREATEST(
                            ar.rastartdate
                            ,pr.startdate)
                       ,aa.allocationstartdate)
              ,to_date('04/01/2012','mm/dd/yyyy'))) 
    * aa.allocationpercent / 100 
    ))
  ,999.99)    AS EFFORT,
aa.projid AS PROJECTID,
ap.projname AS PROJECTNAME,
ar.raname AS RANAME,
ar2.raid AS SUBRANAME
FROM AllocateAssociates aa,ProjecttoRAAssociation pr,addProject ap,AddResearchArea ar, AddResearchArea ar2
WHERE pr.projid = aa.projid (+)
AND aa.projid = ap.projid 
AND pr.raid = ar.raid
AND ar.raid = ar2.parentraid (+)
group by aa.projid,ap.projname,ar.raname,ar2.raid
The problems i am facing
1. I need to get the list of projects which are associated to research area even if they do not have project allocation. Though I have used outer join, I am not getting this result. I used full outer join, but did not yield the expected result. (Since there are multiple tables to be joined. may be something wrong in joining?) (in the data above, proj3 should also be displayed even though it does not have a project allocation)
2. If a research area has more than one research area, there are 2 entries one for each sub research area even though, the project has been associated to only one sub-research area (in the data above, the proj1 has been associated to ra4 only, but the results display both ra4 and ra5)
3. The query displays two entries one for main research area and one for sub research area(provided the project is associated to the correct main and sub research area). But there should be only one entry for a research area containing the name of both main and sub research area

Please help me resolve these issues.
  • 1. Re: Effort using Outer join and parent-child relationship
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    despite I don't like the way you are correlating data, I have created a query that should give you the result you need.
    WITH effort1 AS
    (
      SELECT MONTHS_BETWEEN (1 + LEAST (NVL (ar.raenddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                      , NVL (pr.enddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                      , NVL (aa.enddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                      , SYSDATE)
                               , GREATEST (ar.rastartdate
                                         , pr.startdate
                                         , aa.allocationstartdate
                                         , TO_DATE ('04/01/2012', 'mm/dd/yyyy'))) * aa.allocationpercent / 100 AS effort
           , ap.projid AS projectid
           , ap.projname AS projectname
           , ar.raid AS raid
        FROM projecttoraassociation pr
             LEFT OUTER JOIN allocateassociates aa
                ON (pr.projid = aa.projid) 
           , addproject ap
           , addresearcharea ar
       WHERE     pr.projid = ap.projid
             AND pr.raid = ar.raid
    ),
    areatosubarea AS
    (
       SELECT ar.raid, MIN(ar2.raid) AS "SUBRANAME"
         FROM addresearcharea ar
              LEFT OUTER JOIN addresearcharea ar2
              ON (ar.raid = ar2.parentraid)
        GROUP BY ar.raid
    ),
    effort2 AS
    (  
       SELECT MIN (effort) as "EFFORT"
            , projectid, projectname, raid
         FROM effort1
        GROUP BY projectid, projectname, raid
    ),
    effort3 AS
    (
       SELECT TO_CHAR(SUM(effort), 999.99) AS effort
           , projectid, projectname
           , MIN(raid) AS raid
         FROM effort2
        GROUP BY projectid, projectname
    )
    SELECT ef.effort, ef.projectid, ef.projectname
         , ar.raname, ar2.subraname
      FROM effort3 ef
           LEFT OUTER JOIN addresearcharea ar 
              ON (ef.raid = ar.raid)
           LEFT OUTER JOIN areatosubarea ar2 
              ON (ar.raid = ar2.raid);
    
    Here is the output:
    
    EFFORT  PROJECTID PROJECTNAME RANAME   SUBRANAME
    ------- --------- ----------- -------  ---------
      12.69 proj1     projname1   raname1  ra4
       6.35 proj2     projname2   raname2  ra6
            proj3     projname3   raname3  
    
    {code}
    
    Remember that GREATEST and LEAST function can accept several parameters. No need to nest them.
    
    I have used the WITH statement to create data according to your need but generally I don't like to put table in relation between them and then take only one entry. I use the MIN function to take one entry but I don't really know if this is fitting your needs.
    
    Maybe there might be a better way to do this.
    
    Regards.
    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 2. Re: Effort using Outer join and parent-child relationship
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi,

    I have to revise my previous answer because there is something I don't understand in the data model.
    What I don't understand is how you sum up the effort of a project.

    I have done the following updates on your data:
    UPDATE allocateassociates
       SET allocationpercent=80
     WHERE associateid=2;
     
    UPDATE addresearcharea
       SET rastartdate=TO_DATE('01/05/2012', 'DD/MM/YYYY')
     WHERE raid = 'ra1';
    {code}
    
    With these changes if I've made a query to  list the details of your data and we have the following:
    
    {code:sql}
    WITH effort1 AS
    (
      SELECT TO_CHAR(MONTHS_BETWEEN (1 + LEAST (NVL (ar.raenddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                      , NVL (pr.enddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                      , NVL (aa.enddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                      , SYSDATE)
                               , GREATEST (ar.rastartdate
                                         , pr.startdate
                                         , aa.allocationstartdate
                                         , TO_DATE ('04/01/2012', 'mm/dd/yyyy'))) * aa.allocationpercent / 100, '999.99') AS effort
           , ap.projid AS projectid
           , ap.projname AS projectname
           , ar.raid AS raid
           , aa.associateid
           , aa.allocationpercent
           , ar.rastartdate
           , pr.startdate
           , aa.allocationstartdate
        FROM projecttoraassociation pr
             LEFT OUTER JOIN allocateassociates aa
                ON (pr.projid = aa.projid) 
           , addproject ap
           , addresearcharea ar
       WHERE     pr.projid = ap.projid
             AND pr.raid = ar.raid
    )
    select * from effort1
    order by 2,3,4,5;
    
    The result is the following:
    
    EFFORT  PROJECTID  PROJECTNAME  RAID  ASSOCIATEID ALLOCATIONPERCENT RASTARTDATE STARTDATE ALLOCATIONSTARTDATE
    ------- ---------- ------------ ----- ----------- ----------------- ----------- --------- -------------------
       5.35 proj1      projname1    ra1             1               100 01-MAY-12   01-APR-12 01-APR-12          
       4.28 proj1      projname1    ra1             2                80 01-MAY-12   01-APR-12 01-APR-12          
       6.35 proj1      projname1    ra4             1               100 01-APR-12   01-APR-12 01-APR-12          
       5.08 proj1      projname1    ra4             2                80 01-APR-12   01-APR-12 01-APR-12          
       6.35 proj2      projname2    ra2             3               100 01-APR-12   01-APR-12 01-APR-12          
            proj3      projname3    ra3                                 01-APR-12   01-APR-12 
    {code}
    
    In this case we have different percentage per associateid and different start date per research area. 
    What is the logic to choose the rows and sum the effort?
    
    I'm afraid that my previous query might not fit exactly and I need to understand what you want exactly.
    
    Regards.
    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 3. Re: Effort using Outer join and parent-child relationship
    Pramukh Newbie
    Currently Being Moderated
    Dear Al,

    Thanks for your reply.

    I do understand the problem in co-relating. The table will be split up into 2 tables some time in the future - Research area and sub-research area.

    Unless the user selects a sub research area, the report should calculate the effort based on research area with the sub- research area displayed along in the same row, provided the sub-research area also has been associated with the project.

    To calculate the effort , the greatest of the allocationstartdate, rastartdate and startdate of the association is taken into account as the project may change its research area and sub-research area in its due course of time. In case research and sub-research area association for a project changes or research area itself gets closed, the effort by the associate should go to the previous research area till its end date and to the new research area from the day of association. Similar logic applies for the end date.

    The total effort for the project applies by summing the individual effort of the associates calculated by the above logic.

    Consider the changes below. This might clear things up
    Changed data: 
    RAID PROJID STARTDATE ENDDATE STATUS 
    ra4 proj1 01-APR-12 31-JUL-12 Closed 
    ra1 proj1 01-APR-12 31-JUL-12 Closed 
    
    
    insert into projecttoraassociation values ('ra2','proj1',to_date('08/01/2012','mm/dd/yyyy'),'','Active')
    
    The new results:
    
    EFFORT PROJECTID PROJECTNAME RAID ASSOCIATEID ALLOCATIONPERCENT RASTARTDATE STARTDATE ALLOCATIONSTARTDATE 
    3.00      proj1      projname1      ra1      1      100           01-MAY-12      01-APR-12   01-APR-12 
    2.40      proj1      projname1      ra1      2      80           01-MAY-12      01-APR-12   01-APR-12 
    2.38      proj1      projname1      ra2     1      100           01-APR-12      01-AUG-12   01-APR-12 
    1.91      proj1      projname1      ra2      2      80           01-APR-12      01-AUG-12   01-APR-12 
    4.00      proj1      projname1      ra4      1      100           01-APR-12     01-APR-12   01-APR-12 
    3.20      proj1      projname1      ra4      2      80           01-APR-12     01-APR-12   01-APR-12 
    6.38      proj2      projname2      ra2      3      100           01-APR-12     01-APR-12   01-APR-12 
    -       proj3      projname3      ra3      -       -            01-APR-12      01-APR-12   -  
    Regards,
    Prmk
  • 4. Re: Effort using Outer join and parent-child relationship
    AlbertoFaenza Expert
    Currently Being Moderated
    Pramukh wrote:
    Dear Al,

    The total effort for the project applies by summing the individual effort of the associates calculated by the above logic.
    With the new example that you have provided, could you please clarify what output do you expect?

    I have modified your initial insert to match the new data:
    insert into addresearcharea values ('ra1','raname1',to_date('05/01/2012','mm/dd/yyyy'),'','','Active');
    insert into addresearcharea values ('ra2','raname2',to_date('04/01/2012','mm/dd/yyyy'),'','','Active');
    insert into addresearcharea values ('ra3','raname3',to_date('04/01/2012','mm/dd/yyyy'),'','','Active');
    insert into addresearcharea values ('ra4','raname4',to_date('04/01/2012','mm/dd/yyyy'),'','ra1','Active');
    insert into addresearcharea values ('ra5','raname5',to_date('04/01/2012','mm/dd/yyyy'),'','ra1','Active');
    insert into addresearcharea values ('ra6','raname6',to_date('04/01/2012','mm/dd/yyyy'),'','ra2','Active');
     
    insert into addproject values ('proj1','projname1',to_date('04/01/2012','mm/dd/yyyy'),'','','Research','Active');
    insert into addproject values ('proj2','projname2',to_date('04/01/2012','mm/dd/yyyy'),'','','Research','Active');
    insert into addproject values ('proj3','projname3',to_date('04/01/2012','mm/dd/yyyy'),'','','Research','Active');
     
    insert into projecttoraassociation values ('ra1','proj1',to_date('04/01/2012','mm/dd/yyyy'),to_date('07/31/2012','mm/dd/yyyy'),'Active');
    insert into projecttoraassociation values ('ra2','proj1',to_date('08/01/2012','mm/dd/yyyy'),'','Active');
    insert into projecttoraassociation values ('ra4','proj1',to_date('04/01/2012','mm/dd/yyyy'),to_date('07/31/2012','mm/dd/yyyy'),'Active');
    insert into projecttoraassociation values ('ra2','proj2',to_date('04/01/2012','mm/dd/yyyy'),'','Active');
    insert into projecttoraassociation values ('ra3','proj3',to_date('04/01/2012','mm/dd/yyyy'),'','Active');
     
    insert into allocateassociates values ('proj1',1,to_date('04/01/2012','mm/dd/yyyy'),100,'');
    insert into allocateassociates values ('proj1',2,to_date('04/01/2012','mm/dd/yyyy'),80,'');
    insert into allocateassociates values ('proj2',3,to_date('04/01/2012','mm/dd/yyyy'),100,'');
    {code}
    
    Then I have run this query which is returning data like yours (minor differences due to SYSDATE change)
    
    {code:sql}
    WITH effort1 AS
    (
      SELECT TO_CHAR(MONTHS_BETWEEN (1 + LEAST (NVL (ar.raenddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                      , NVL (pr.enddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                      , NVL (aa.enddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                      , SYSDATE)
                               , GREATEST (ar.rastartdate
                                         , pr.startdate
                                         , aa.allocationstartdate
                                         , TO_DATE ('04/01/2012', 'mm/dd/yyyy'))) * aa.allocationpercent / 100, '999.99') AS effort
           , ap.projid AS projectid
           , ap.projname AS projectname
           , ar.raid AS raid
           , aa.associateid
           , aa.allocationpercent
           , ar.rastartdate
           , pr.startdate
           , aa.allocationstartdate
        FROM projecttoraassociation pr
             LEFT OUTER JOIN allocateassociates aa
                ON (pr.projid = aa.projid) 
           , addproject ap
           , addresearcharea ar
       WHERE     pr.projid = ap.projid
             AND pr.raid = ar.raid
    )
    select * from effort1
    order by 2,3,4,5;
    
    The output is:
    
    EFFORT  PROJECTID                      PROJECTNAME                    RAID                           ASSOCIATEID ALLOCATIONPERCENT RASTARTDATE STARTDATE ALLOCATIONSTARTDATE
    ------- ------------------------------ ------------------------------ ------------------------------ ----------- ----------------- ----------- --------- -------------------
       3.00 proj1                          projname1                      ra1                                      1               100 01-MAY-12   01-APR-12 01-APR-12          
       2.40 proj1                          projname1                      ra1                                      2                80 01-MAY-12   01-APR-12 01-APR-12          
       2.40 proj1                          projname1                      ra2                                      1               100 01-APR-12   01-AUG-12 01-APR-12          
       1.92 proj1                          projname1                      ra2                                      2                80 01-APR-12   01-AUG-12 01-APR-12          
       4.00 proj1                          projname1                      ra4                                      1               100 01-APR-12   01-APR-12 01-APR-12          
       3.20 proj1                          projname1                      ra4                                      2                80 01-APR-12   01-APR-12 01-APR-12          
       6.40 proj2                          projname2                      ra2                                      3               100 01-APR-12   01-APR-12 01-APR-12          
            proj3                          projname3                      ra3                                                          01-APR-12   01-APR-12                    
    {code}
    
    Coming back to your initial request what do you expect as effort for proj1 in your report:
    3.00 + 2.40 + 2.40 + 1.92 + 4.00 + 3.20 = 16.92 
    
    Or a different logic? And which area do you want to print on the line of proj1? Is the first area involved in that project? And subarea related to that area?
    
    Sorry but I did not get your logic.
    
    Regards.
    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 5. Re: Effort using Outer join and parent-child relationship
    Pramukh Newbie
    Currently Being Moderated
    Yes, for proj1 the effort is 16.92. But what I am looking for is Research area wise effort report. For a given research area, i need to know the complete effort.

    For the updated data you provided, proj1 has been split across two different research areas, hence there are two rows, one for each research area, Similar for raname2, since it has 2 projects under it. No effort for proj3 as it does not have any associates working under it.

    Let us keep aside sub research area for the moment
    The required output
    EFFORT      PROJECTID      PROJECTNAME      RANAME 
    5.40      proj1           projname1      raname1
    4.32      proj1           projname1      raname2
    6.40      proj2           projname2     raname2
    -       proj3          projname3     raname3
    Regards,
    Prmk
  • 6. Re: Effort using Outer join and parent-child relationship
    AlbertoFaenza Expert
    Currently Being Moderated
    Pramukh wrote:
    Yes, for proj1 the effort is 16.92. But what I am looking for is Research area wise effort report. For a given research area, i need to know the complete effort.

    For the updated data you provided, proj1 has been split across two different research areas, hence there are two rows, one for each research area, Similar for raname2, since it has 2 projects under it. No effort for proj3 as it does not have any associates working under it.

    Let us keep aside sub research area for the moment
    The required output
    EFFORT      PROJECTID      PROJECTNAME      RANAME 
    5.40      proj1           projname1      raname1
    4.32      proj1           projname1      raname2
    6.40      proj2           projname2     raname2
    -       proj3          projname3     raname3
    Regards,
    Prmk
    Hi Pramukh,

    I think this is quite easy then. It's just a matter of aggregating data.
    I think you forgot raname4 though.

    Here is the statement and the output:
    SELECT TO_CHAR(SUM(MONTHS_BETWEEN (1 + LEAST (NVL (ar.raenddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                    , NVL (pr.enddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                    , NVL (aa.enddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                    , SYSDATE)
                             , GREATEST (ar.rastartdate
                                       , pr.startdate
                                       , aa.allocationstartdate
                                       , TO_DATE ('04/01/2012', 'mm/dd/yyyy'))) * aa.allocationpercent / 100
                      ), '999.99'
                  ) AS effort
         , ap.projid AS projectid
         , ap.projname AS projectname
         , ar.raname AS raname
      FROM projecttoraassociation pr
           LEFT OUTER JOIN allocateassociates aa
              ON (pr.projid = aa.projid) 
         , addproject ap
         , addresearcharea ar
     WHERE     pr.projid = ap.projid
           AND pr.raid = ar.raid
     GROUP BY ap.projid, ap.projname, ar.raname
     ORDER BY ap.projid, ap.projname, ar.raname;
    
    output:
    EFFORT  PROJECTID PROJECTNAME RANAME 
    ------- --------- ----------- -------
       5.40 proj1     projname1   raname1
       4.32 proj1     projname1   raname2
       7.20 proj1     projname1   raname4
       6.40 proj2     projname2   raname2
            proj3     projname3   raname3
    {code}
    
    Regards.
    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  • 7. Re: Effort using Outer join and parent-child relationship
    Pramukh Newbie
    Currently Being Moderated
    Al,

    Thank you very much.

    Given, the fact that a project can at most have one Active Research area and one Active sub-research area association, is it possible to have the corresponding sub-research area displayed in the same row.

    In the below data, there is no subraname for any of the other rows other than the first row, as there is no corresponding entry for sub ra name for any other projects in projecttoraassociation table
    EFFORT  PROJECTID PROJECTNAME RANAME SUBRANAME
    ------- --------- ----------- ------- ------
       5.40 proj1     projname1   raname1 raname4
       4.32 proj1     projname1   raname2 
       7.20 proj1     projname1   -           raname4
       6.40 proj2     projname2   raname2
            proj3     projname3   raname3
    Regards,

    Prmk
  • 8. Re: Effort using Outer join and parent-child relationship
    Pramukh Newbie
    Currently Being Moderated
    Al,

    Thank you very much.

    Given, the fact that a project can at most have one Active Research area and one Active sub-research area association, is it possible to have the corresponding sub-research area displayed in the same row.

    In the below data, there is no subraname for any of the other rows other than the first row, as there is no corresponding entry for sub ra name for any other projects in projecttoraassociation table
    EFFORT  PROJECTID PROJECTNAME RANAME SUBRANAME
    ------- --------- ----------- ------- ------
       5.40 proj1     projname1   raname1 raname4
       4.32 proj1     projname1   raname2 
       7.20 proj1     projname1   -           raname4
       6.40 proj2     projname2   raname2
            proj3     projname3   raname3
    Regards,

    Prmk
  • 9. Re: Effort using Outer join and parent-child relationship
    Pramukh Newbie
    Currently Being Moderated
    Al,

    Thank you very much.

    Given, the fact that a project can at most have one Active Research area and one Active sub-research area association, is it possible to have the corresponding sub-research area displayed in the same row.

    In the below data, there is no subraname for any of the other rows other than the first row, as there is no corresponding entry for sub ra name for any other projects in projecttoraassociation table
    EFFORT  PROJECTID PROJECTNAME RANAME SUBRANAME
    ------- --------- ----------- ------- ------
       5.40 proj1     projname1   raname1 raname4
       4.32 proj1     projname1   raname2 
       7.20 proj1     projname1   -           raname4
       6.40 proj2     projname2   raname2
            proj3     projname3   raname3
    Regards,

    Prmk
  • 10. Re: Effort using Outer join and parent-child relationship
    Pramukh Newbie
    Currently Being Moderated
    Al,

    Thank you very much.

    Given, the fact that a project can at most have one Active Research area and one Active sub-research area association, is it possible to have the corresponding sub-research area displayed in the same row.

    In the below data, there is no subraname for any of the other rows other than the first row, as there is no corresponding entry for sub ra name for any other projects in projecttoraassociation table
    EFFORT  PROJECTID PROJECTNAME RANAME SUBRANAME
    ------- --------- ----------- ------- ------
       5.40 proj1     projname1   raname1 raname4
       4.32 proj1     projname1   raname2 
       7.20 proj1     projname1   -           raname4
       6.40 proj2     projname2   raname2
            proj3     projname3   raname3
    Regards,

    Prmk
  • 11. Re: Effort using Outer join and parent-child relationship
    Pramukh Newbie
    Currently Being Moderated
    Al,

    Thank you very much.

    Given, the fact that a project can at most have one Active Research area and one Active sub-research area association, is it possible to have the corresponding sub-research area displayed in the same row.

    In the below data, there is no subraname for any of the other rows other than the first row, as there is no corresponding entry for sub ra name for any other projects in projecttoraassociation table
    EFFORT  PROJECTID PROJECTNAME RANAME SUBRANAME
    ------- --------- ----------- ------- ------
       5.40 proj1     projname1   raname1 raname4
       4.32 proj1     projname1   raname2 
       7.20 proj1     projname1   -           raname4
       6.40 proj2     projname2   raname2
            proj3     projname3   raname3
    Regards,

    Prmk
  • 12. Re: Effort using Outer join and parent-child relationship
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Pramukh,

    It can be done. We have to distinguish 2 cases:

    a) when the research area in projecttoraassociation is not a subarea we have to check if it has subareas and in this case print one subarea the same line. Question: how do you select the subarea? Do you take the first area in a particular order? ra1 has ra4 and ra5 as subareas. Which criteria do use to select ra4 and not ra5?
    And why for research area ra2 there is no subarea despite it has ra6 as subarea?

    b) when the research area in projecttoraassociation is a subarea then print it under column subarea.

    I will propose you 2 solutions and you choose which one better fits to your need:
    WITH areatosubarea AS
    (
       SELECT ar.raid
            , ar2.raid AS subraid
            , ar2.raname subraname
            , ROW_NUMBER() OVER (PARTITION BY ar.raid ORDER BY ar2.raid) AS rn
         FROM addresearcharea ar
              JOIN addresearcharea ar2
              ON (ar.raid = ar2.parentraid)
    )
    , prjeffort AS
    (
       SELECT MONTHS_BETWEEN ( 1 + LEAST ( NVL (ar.raenddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                        , NVL (pr.enddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                        , NVL (aa.enddate, TO_DATE ('04/01/2100', 'mm/dd/yyyy'))
                                        , SYSDATE
                                        )
                             , GREATEST ( ar.rastartdate
                                        , pr.startdate
                                        , aa.allocationstartdate
                                        , TO_DATE ('04/01/2012', 'mm/dd/yyyy')
                                        )
                             ) * aa.allocationpercent / 100 AS effort
            , ap.projid AS projectid
            , ap.projname AS projectname
            , CASE WHEN ar.parentraid IS NULL THEN ar.raname END AS raname
            , CASE WHEN ar.parentraid IS NOT NULL THEN ar.raname ELSE sa.subraname END AS subraname
         FROM projecttoraassociation pr
              JOIN addproject ap 
                 ON (pr.projid = ap.projid)
              JOIN addresearcharea ar 
                 ON (pr.raid = ar.raid)
              LEFT OUTER JOIN allocateassociates aa
                 ON (pr.projid = aa.projid)
              LEFT OUTER JOIN areatosubarea sa
                 ON (ar.raid = sa.raid AND sa.rn = 1) 
    )
    SELECT TO_CHAR(sum(effort), '999.99') AS effort
         , projectid, projectname, raname, subraname  
      FROM prjeffort
     GROUP BY projectid, projectname, raname, subraname
     ORDER BY projectid, projectname, raname, subraname; 
    
    output:
    EFFORT  PROJECTID PROJECTNAME RANAME  SUBRANAME
    ------- --------- ----------- ------- ---------
       5.40 proj1     projname1   raname1 raname4  
       4.39 proj1     projname1   raname2 raname6  
       7.20 proj1     projname1           raname4  
       6.44 proj2     projname2   raname2 raname6  
            proj3     projname3   raname3          
    {code}
    
    If you want to have subarea only for subareas which are in table projecttoraassociation then you can modify the first query in this way:
    
    {code:sql}
    WITH areatosubarea AS
    (
       SELECT ar.raid
            , ar2.raid AS subraid
            , ar2.raname subraname
            , ROW_NUMBER() OVER (PARTITION BY ar.raid ORDER BY ar2.raid) AS rn
         FROM addresearcharea ar
              JOIN addresearcharea ar2
              ON (ar.raid = ar2.parentraid)
        WHERE EXISTS (SELECT 1 FROM projecttoraassociation pr WHERE pr.raid = ar2.raid)  
    )
    ....
    
    output:
    EFFORT  PROJECTID PROJECTNAME RANAME  SUBRANAME
    ------- --------- ----------- ------- ---------
       5.40 proj1     projname1   raname1 raname4  
       4.39 proj1     projname1   raname2
       7.20 proj1     projname1           raname4  
       6.44 proj2     projname2   raname2
            proj3     projname3   raname3          
    {code}
    
    
    Regards.
    Al
    
    Edited by: Alberto Faenza on Oct 13, 2012 1:57 PM
    Query and examples added                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 13. Re: Effort using Outer join and parent-child relationship
    Pramukh Newbie
    Currently Being Moderated
    Thank your very much. works great :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points