This discussion is archived
7 Replies Latest reply: Nov 20, 2012 8:05 AM by Frank Kulash RSS

Calculating Effort where there is duplicate project to lab association

Pramukh Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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