7 Replies Latest reply: Nov 20, 2012 10:05 AM by Frank Kulash RSS

    Calculating Effort where there is duplicate project to lab association

    Pramukh
      Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product

      I have the following tables which I have used to get the effort of an associate.
      CREATE TABLE  "ADDASSOCIATE" 
         (     "ASSOCIATEID" NUMBER(9,0) NOT NULL ENABLE, 
           "STARTDATE" DATE, 
           "ENDDATE" DATE, 
           "ASSOCIATENAME" VARCHAR2(20), 
            PRIMARY KEY ("ASSOCIATEID") ENABLE
         )
      
      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  "ADDLAB" 
         (     "LABID" VARCHAR2(30) NOT NULL ENABLE, 
           "LABTYPE" VARCHAR2(30), 
           "LABNAME" VARCHAR2(20), 
            PRIMARY KEY ("LABID") ENABLE
         )
      
      CREATE TABLE  "PROJECTTOLABASSOCIATION" 
         (     "LABID" VARCHAR2(30) NOT NULL ENABLE, 
           "PROJID" VARCHAR2(30) NOT NULL ENABLE, 
           "STARTDATE" DATE, 
           "ENDDATE" DATE, 
           "STATUS" VARCHAR2(30), 
            PRIMARY KEY ("LABID", "PROJID") ENABLE
         )
      
      insert into addassociate values (1,to_date('04/01/2012','mm/dd/yyyy'),'','Asso1')
      insert into addassociate values (2,to_date('04/01/2012','mm/dd/yyyy'),'','Asso2')
      insert into addassociate values (3,to_date('04/01/2012','mm/dd/yyyy'),'','Asso3')
      insert into addassociate values (4,to_date('04/01/2012','mm/dd/yyyy'),'','Asso3')
      
      insert into addproject values ('proj1','projname 1', to_date('04/01/2012','mm/dd/yyyy'),'','','Research','Status')
      insert into addproject values ('proj2','projname 2', to_date('04/01/2012','mm/dd/yyyy'),'','','Research','Status')
      insert into addproject values ('proj3','projname 3', to_date('04/01/2012','mm/dd/yyyy'),'','','Research','Status')
      
      insert into ALLOCATEASSOCIATES values ('proj1',1, to_date('04/01/2012','mm/dd/yyyy'),50,'')
      insert into ALLOCATEASSOCIATES values ('proj2',1, to_date('04/01/2012','mm/dd/yyyy'),50,'')
      insert into ALLOCATEASSOCIATES values ('proj2',2, to_date('04/01/2012','mm/dd/yyyy'),100,'')
      insert into ALLOCATEASSOCIATES values ('proj3',3, to_date('04/01/2012','mm/dd/yyyy'),100,'')
      insert into ALLOCATEASSOCIATES values ('proj1',4, to_date('04/01/2012','mm/dd/yyyy'),100,'')
      
      insert into ADDLAB values ('lab1','Research','lab name 1')
      insert into ADDLAB values ('lab2','Research','lab name 2')
      insert into ADDLAB values ('lab3','Research','lab name 3')
      
      insert into PROJECTTOLABASSOCIATION values ('lab1','proj1',to_date('04/01/2012','mm/dd/yyyy'),'','Status')
      insert into PROJECTTOLABASSOCIATION values ('lab2','proj1',to_date('04/01/2012','mm/dd/yyyy'),'','Status')
      insert into PROJECTTOLABASSOCIATION values ('lab2','proj2',to_date('04/01/2012','mm/dd/yyyy'),'','Status')
      insert into PROJECTTOLABASSOCIATION values ('lab3','proj3',to_date('04/01/2012','mm/dd/yyyy'),'','Status')
      The below query works for getting the effort associate wise
      select ap.projid,ap.projname,al.labname,aa.associateid,
      to_char(MONTHS_BETWEEN(1 + LEAST(NVL(aa.endDate, to_date('04/01/2100','mm/dd/yyyy')),sysDate)
      , GREATEST(aa.allocationstartdate
      ,ap.projStartDate)) 
      * aa.allocationpercent / 100,'999.99') AS Effort
      from 
      AllocateAssociates aa, AddAssociate a, AddProject ap, ProjecttoLabAssociation pl,AddLab al
      WHERE aa.projid = ap.projid 
      AND a.associateid = aa.associateid
      AND pl.projID = ap.projID 
      AND al.labID = pl.labID 
      There is a short coming in the query above. For eg;- proj1 has been associated to both lab1 and lab2. Hence the result contains 2 entries one for lab1 and one for lab2. Where the total effort should have been 3.6 person months for associate with id 1 for proj1 , the effort is displayed as 3.6 twice, one entry for lab1 and another for lab2.
      Hence for the above scenario, the allocation of the associate to a lab should be taken into account for calculating the effort. The lab allocation is got from the below table
      CREATE TABLE  "ALLOCATEASSOCIATESTOLAB" 
         (     "LABID" VARCHAR2(30) NOT NULL ENABLE, 
           "ASSOCIATEID" NUMBER(*,0), 
           "ALLOCATIONSTARTDATE" DATE, 
           "ALLOCATIONPERCENT" NUMBER(*,0), 
           "ENDDATE" DATE, 
            PRIMARY KEY ("LABID", "ASSOCIATEID") ENABLE
         )
      
      insert into ALLOCATEASSOCIATESTOLAB values ('lab1',1, to_date('04/01/2012','mm/dd/yyyy'),100,'')
      insert into ALLOCATEASSOCIATESTOLAB values ('lab2',2, to_date('04/01/2012','mm/dd/yyyy'),100,'')
      insert into ALLOCATEASSOCIATESTOLAB values ('lab3',3, to_date('04/01/2012','mm/dd/yyyy'),100,'')
      insert into ALLOCATEASSOCIATESTOLAB values ('lab2',4, to_date('04/01/2012','mm/dd/yyyy'),100,'')
      Below is the expected result:
      PROJID PROJNAME LABNAME ASSOCIATEID EFFORT 
      proj1 projname1 lab name 1 1 3.6 
      proj2 projname2 lab name 2 2 3.6 
      proj3 projname3 lab name 3 3 7.19 
      proj1 projname1 lab name 2 4 7.19 
      Can you pls help


      Regards
      Prmk
        • 1. Re: Calculating Effort where there is duplicate project to lab association
          Frank Kulash
          Hi,

          This (almost) gets the results you requested:
          SELECT    p.projid
          ,       p.projname
          ,       l.labname
          ,       ap.associateid
          ,       TO_CHAR ( MONTHS_BETWEEN ( 1 + LEAST ( NVL ( ap.endDate
                                                                 , TO_DATE ('04/01/2100','mm/dd/yyyy')
                                             )
                                          , SYSDATE
                                          )
                                 , GREATEST ( ap.allocationstartdate
                                              , p.projStartDate
                                         )
                                 ) * ap.allocationpercent 
                                   / 100
                      , '999.99'
                      )     AS effort
          FROM      allocateassociates       ap
          ,        addassociate            a
          ,       addproject             p
          ,       projecttolabassociation pl
          ,       addlab            l
          ,       allocateassociatestolab al               -- *****  NEW  *****
          WHERE        ap.projid             = p.projid 
          AND       a.associateid            = ap.associateid
          AND       pl.projid            = p.projid 
          AND        l.labid            = pl.labid
          AND       a.associateid            = al.associateid     -- *****  NEW  *****
          AND       al.labid            = l.labid          -- *****  NEW  *****
          ORDER BY  projid
          ,            projname
          ,            labname
          ;
          My results are what you requested, except that effort is a little low:
          PROJID PROJNAME   LABNAME    ASSOCIATEID EFFORT
          ------ ---------- ---------- ----------- -------
          proj1  projname 1 lab name 1           1    3.59
          proj1  projname 1 lab name 2           4    7.18
          proj2  projname 2 lab name 2           2    7.18
          proj3  projname 3 lab name 3           3    7.18
          That may be due to effort being dependent on SYSDATE. If your output depends on the current date and time, then say what the current date and time are when you post the desired results. Also, I don't see why the effort for 'proj2' should be 3.6, and not 7.19.

          The query above is exactly what you posted, except that I added allocateassociatestolab to the FROM clause, and joined it to 2 other tables in the WHERE clause, as marked. I also changed the table aliases, and added an ORDER BY clause, which you don't have to do.

          Always explain how you get the results you want from the data given. It helps if you say what each row of the results represents, and what part each column of the given tables plays in this problem. For example, the query I posted above doesn't use the DATE columns in allocateassociatestolab or projecttolabassociation. When all the dates are the same (April 1 2012 in your sample data) it's hard to guess why they might be needed.
          • 2. Re: Calculating Effort where there is duplicate project to lab association
            Pramukh
            Frank,

            Thanks for your reply

            Yes, the effort for proj2 should have been 7.19 and not 3.2. I wrote the results manually. Hence the error.
            The dates in allocateassociatestolab or projecttolabassociation are of no use in this query. They are used for other scenarios.

            There is one additional condition which I must have written in my previous post.
            'An associate can be working for a project which does not necessarily belong the the lab he is allocated to'

            Associate '4' is allocated to 'lab2'. 'lab2' has 2 projects under it - 'proj1' and 'proj2'. Consider the following changes in the data
            update ALLOCATEASSOCIATES set ALLOCATIONPERCENT = 50 where associateid = 4
            insert into ALLOCATEASSOCIATES values ('proj3',4, to_date('04/01/2012','mm/dd/yyyy'),50,'')
            Associate '4' has been allocated to 'proj3' which belongs to 'lab3'
            Now, the associates' allocation to proj3 (under lab3) should also be accounted for in the effort calculation. Its only upon hitting into 2 entries into projecttolabassociation table for the same project id, the lab allocation should be taken into account.
            PROJID PROJNAME   LABNAME    ASSOCIATEID EFFORT
            ------ ---------- ---------- ----------- -------
            proj1  projname 1 lab name 1           1    3.64
            proj1  projname 1 lab name 2           4    3.64
            proj2  projname 2 lab name 2           2    7.28
            proj3  projname 3 lab name 3           3    7.28
            proj3  projname 3 lab name 3           4    3.64
            sysdate - 08 Nov 2012 1:15PM IST(+5:30 GST)


            Regards
            Prmk
            • 3. Re: Calculating Effort where there is duplicate project to lab association
              Frank Kulash
              Hi,

              Sorry, I don't understand the requirements.
              Explain, step by step, how you get the results you want, especially for associateids 1 and 4.
              Pramukh wrote:
              Now, the associates' allocation to proj3 (under lab3) should also be accounted for in the effort calculation. Its only upon hitting into 2 entries into projecttolabassociation table for the same project id, the lab allocation should be taken into account.
              Are you saying that the allocateassociatestolab table only matters when there are 2 (or more) rows in projecttolabassociation with the same projid?
              PROJID PROJNAME   LABNAME    ASSOCIATEID EFFORT
              ------ ---------- ---------- ----------- -------
              proj1  projname 1 lab name 1           1    3.64
              proj1  projname 1 lab name 2           4    3.64
              proj2  projname 2 lab name 2           2    7.28
              proj3  projname 3 lab name 3           3    7.28
              proj3  projname 3 lab name 3           4    3.64
              I don't understand why you want to show a relationship between
              associateid=4 and labname='lab name 3', but not between
              associateid=4 and labname='lab name 1' or
              associateid=1 and labname='lab name 2'.
              • 4. Re: Calculating Effort where there is duplicate project to lab association
                Pramukh
                Hi,
                Are you saying that the allocateassociatestolab table only matters when there are 2 (or more) rows in projecttolabassociation with the same projid?
                Yes you are right. only when there are 2 or more rows in projecttolabassociation with the same projid , allocateassociatestolab matters.

                I m just copy pasting the same expected result as I did in my previous post.
                PROJID PROJNAME   LABNAME    ASSOCIATEID EFFORT
                ------ ---------- ---------- ----------- -------
                proj1  projname 1 lab name 1           1    3.64
                proj1  projname 1 lab name 2           4    3.64
                proj2  projname 2 lab name 2           2    7.28
                proj3  projname 3 lab name 3           3    7.28
                proj3  projname 3 lab name 3           4    3.64
                For the below row, the project is associated to lab1 and lab2. In cases like these where there are more than 2 entries in lab to project association, the allocation of the associate to the lab should be considered. In the example below, the associate has been allocated to lab1. Hence the effort of the associate to the project should go to lab1 and not to lab2.
                PROJID PROJNAME   LABNAME    ASSOCIATEID EFFORT
                ------ ---------- ---------- ----------- -------
                proj1  projname 1 lab name 1           1    3.64
                similar is the case below. Here, associate 4 has been allocated to lab2. Hence the effort for this associate 4 to proj1 goes to lab2
                PROJID PROJNAME   LABNAME    ASSOCIATEID EFFORT
                ------ ---------- ---------- ----------- -------
                proj1  projname 1 lab name 2           4    3.64
                All the entries below are straight forward, all the projects below have only one lab association. Hence, there is no need to check for lab allocation of the associate
                PROJID PROJNAME   LABNAME    ASSOCIATEID EFFORT
                ------ ---------- ---------- ----------- -------
                proj2  projname 2 lab name 2           2    7.28
                proj3  projname 3 lab name 3           3    7.28
                proj3  projname 3 lab name 3           4    3.64
                Associate 4 has been allocated to proj3 also. And proj3 has been associated to lab3. Now, it does not matter to which lab, associate 4 belongs to as proj3 has been associated to only one lab which is lab3. Hence the effort for the associate 4 under proj3 should go to lab3 and the not lab2(lab2 is the lab allocation of associate 4)
                PROJID PROJNAME   LABNAME    ASSOCIATEID EFFORT
                ------ ---------- ---------- ----------- -------
                proj3  projname 3 lab name 3           4    3.64
                Regards
                Prmk
                • 5. Re: Calculating Effort where there is duplicate project to lab association
                  Frank Kulash
                  Hi,

                  I still don't understand why the results should not include:
                  PROJID PROJNAME   LABNAME    ASSOCIATEID EFFORT
                  ------ ---------- ---------- ----------- -------
                  proj2  projname 2 lab name 2           1    3.83
                  in addition to the 5 rows of results you posted. I know there is nothing in allocateassociatestolab that relates labid='lab2' to associateid=1, but this is for projid='proj2', and that project only has 1 row in projecttolabassociation, so allocateassociatestolab doesn't matter.
                  • 6. Re: Calculating Effort where there is duplicate project to lab association
                    Pramukh
                    Hi

                    Yes you are right. The one you have mentioned should definitely be included. Not sure how I over-looked that. Sorry for the confusion

                    Regards
                    Prmk
                    • 7. Re: Calculating Effort where there is duplicate project to lab association
                      Frank Kulash
                      Hi,

                      This does what you requested:
                      WITH        pl      AS
                      (
                           SELECT     labid
                           ,     projid
                           ,     startdate
                           ,     enddate
                           ,     COUNT (DISTINCT labid)  OVER (PARTITION BY  projid)     AS lab_cnt
                           FROM     projecttolabassociation
                      )
                      SELECT    p.projid
                      ,       p.projname
                      ,       l.labname
                      ,       ap.associateid
                      ,       TO_CHAR ( MONTHS_BETWEEN ( 1 + LEAST ( NVL ( ap.endDate
                                                                             , TO_DATE ('04/01/2100','mm/dd/yyyy')
                                                         )
                                                      , SYSDATE
                                                      )
                                             , GREATEST ( ap.allocationstartdate
                                                          , p.projStartDate
                                                     )
                                             ) * ap.allocationpercent 
                                               / 100
                                  , '999.99'
                                  )     AS effort
                      FROM      addproject             p
                      JOIN       allocateassociates       ap  ON   ap.projid          = p.projid 
                      JOIN        addassociate            a   ON   a.associateid    = ap.associateid
                      JOIN                        pl  ON   pl.projid         = p.projid 
                      JOIN       addlab            l   ON   l.labid         = pl.labid
                      LEFT JOIN allocateassociatestolab al  ON   al.associateid   = a.associateid
                                                       AND  al.labid         = l.labid
                                                AND  pl.lab_cnt       > 1
                      WHERE     pl.lab_cnt  = 1
                      OR       al.labid     = l.labid
                      ORDER BY  projid
                      ,            projname
                      ,            labname
                      ;
                      Output (when SYSDATE is 9-Nov-2012 00:00:00):
                      PROJID PROJNAME   LABNAME    ASSOCIATEID EFFORT
                      ------ ---------- ---------- ----------- -------
                      proj1  projname 1 lab name 1           1    3.65
                      proj1  projname 1 lab name 2           4    3.65
                      proj2  projname 2 lab name 2           1    3.65
                      proj2  projname 2 lab name 2           2    7.29
                      proj3  projname 3 lab name 3           3    7.29
                      proj3  projname 3 lab name 3           4    3.65