13 Replies Latest reply: Oct 16, 2012 9:54 AM by Pramukh RSS

    Effort using Outer join and parent-child relationship

    Pramukh
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  Thank your very much. works great :)