Clarity

Expand all | Collapse all

SQL Query for Parent Task

Jump to Best Answer
  • 1.  SQL Query for Parent Task

    Posted 11-10-2015 06:35 AM

    Hi All, Do you have any SQL query to extract the immediate parent of any given task in CA Clarity ? The query should fetch null, if the task is not having any parent task.



  • 2.  Re:  SQL Query for Parent Task

    Posted 11-10-2015 06:51 AM

    On the PRTASK table, the parent task will be the one with the highest PRWBSSEQUENCE less than your queried task and with a PRWBSLEVEL one less than the PRWBSLEVEL of your queried task (and the same PRPROJECTID obviously!)



  • 3.  Re:  SQL Query for Parent Task

    Posted 11-10-2015 08:07 AM

    Hi Dave, Thanks for the answer.. so here is a simple query from me.. May you suggest if this work ..

     

    select t1.prid parentask_id, t1.prname parent_taskname

    from prtask t

    inner join prtask t1 on t1.prwbslevel = t.prwbslevel -1 and t1.prprojectid = t.prprojectid

    where t.prid = {:param_child_task} and t.prprojectid = {:param_project}



  • 4.  Re:  SQL Query for Parent Task
    Best Answer

    Posted 11-10-2015 08:35 AM

    You need some logic around PRWBSSEQUENCE as well - you want the t1 record with the MAX(PRWBSSEQUENCE) < the PRWBSSEQUENCE of the t record

     

    (if you query PRWBSLEVEL and PRWBSSEQUENCE for a project you should see how the data hangs together, its not really a master-detail relationship, its a little strange how the PRWBSLEVEL and PRWBSSEQUENCE defines the work-breakdown-structure, but its fairly simple to understand)

     

    --

     

    SELECT t1.prid parentask_id, t1.prname parent_taskname

    FROM prtask t

       , prtask t1

    WHERE t1.prprojectid = t.prprojectid

      AND t1.prwbslevel < t.prwbslevel

      AND t1.PRWBSSEQUENCE = ( SELECT max(PRWBSSEQUENCE)

                              FROM prtask t2

                             WHERE t2.prprojectid = t.prprojectid

                               AND t2.prwbslevel < t.prwbslevel

                           AND t2.PRWBSSEQUENCE < t.PRWBSSEQUENCE

                           )

      AND t.prid = {:param_child_task}



  • 5.  Re:  SQL Query for Parent Task

    Posted 11-10-2015 09:51 AM

    Thanks Dave for guiding into proper direction..I will test with this query in our system and let you all know. Many thanks again for the help



  • 6.  Re:  SQL Query for Parent Task

    Posted 11-10-2015 08:55 AM

    You should get some idea from here -

     

    Timesheet - Task cannot be added into timesheet

     

    SELECT * FROM ( SELECT T.PRID AS TASK_ID

    ,T.PRNAME AS NAME

    ,T.PREXTERNALID AS UNIQUE_NAME

    ,T.PRSTART AS START_DATE

    ,T.PRFINISH AS FINISH_DATE

    ,T.PRSTATUS AS TASK_STATUS

    ,T.PRWBSSEQUENCE

    ,P.ID AS PROJECT_ID

    ,P.NAME AS PROJECT_NAME

    ,P.CODE AS PROJECT_UNIQUE_NAME

    FROM INV_INVESTMENTS P, INV_PROJECTS PRJ, PRTEAM

    , PRTASK T , (SELECT prtaskid, prresourceid FROM PRASSIGNMENT WHERE PRRESOURCEID=<ResourceID>) A

    WHERE T.PRID = A.PRTASKID AND PRTEAM.PRRESOURCEID=<ResourceID>

    AND PRTEAM.PRISOPEN <> 0

    AND P.ID = PRTEAM.PRPROJECTID

    AND P.IS_ACTIVE <> 0

    AND PRJ.PRID = P.ID AND PRJ.IS_TEMPLATE = 0

    AND P.IS_OPEN_FOR_TE <> 0 AND P.TRACK_MODE = 2

    AND T.PRPROJECTID = P.ID

    AND T.PRISMILESTONE = 0

    AND T.PRISTASK <> 0

    AND T.IS_OPEN_TE = 1 AND T.PRSTATUS <> 2 MINUS

    SELECT T.PRID AS TASK_ID

    ,T.PRNAME AS NAME

    ,T.PREXTERNALID AS UNIQUE_NAME

    ,T.PRSTART AS START_DATE

    ,T.PRFINISH AS FINISH_DATE

    ,T.PRSTATUS AS TASK_STATUS

    ,T.PRWBSSEQUENCE

    ,P.ID AS PROJECT_ID

    ,P.NAME AS PROJECT_NAME

    ,P.CODE AS PROJECT_UNIQUE_NAME

    FROM INV_INVESTMENTS P, INV_PROJECTS PRJ, PRTEAM

    , PRTASK T , (SELECT prtaskid, prresourceid FROM PRASSIGNMENT WHERE PRRESOURCEID=<ResourceID>) A

    WHERE T.PRID = A.PRTASKID AND PRTEAM.PRRESOURCEID=<ResourceID>

    AND PRTEAM.PRISOPEN <> 0

    AND P.ID = PRTEAM.PRPROJECTID

    AND P.IS_ACTIVE <> 0

    AND PRJ.PRID = P.ID AND PRJ.IS_TEMPLATE = 0

    AND P.IS_OPEN_FOR_TE <> 0 AND P.TRACK_MODE = 2

    AND T.PRPROJECTID = P.ID

    AND T.PRISMILESTONE = 0

    AND T.PRISTASK <> 0

    AND T.IS_OPEN_TE = 1

    AND (EXISTS

    (SELECT PARENT.PRID FROM PRTASK PARENT

    WHERE PARENT.PRPROJECTID = P.ID

    AND PARENT.PRWBSSEQUENCE < T.PRWBSSEQUENCE

    AND PARENT.WBS_NNBSEQ > T.PRWBSSEQUENCE

    AND PARENT.IS_OPEN_TE = 0)

    OR T.PRID IN

    (SELECT DISTINCT A1.PRTASKID

    FROM PRASSIGNMENT A1, PRTIMEENTRY TE1

    WHERE TE1.PRASSIGNMENTID = A1.PRID

    AND TE1.PRTIMESHEETID = <TimesheetId>)

    ) ) SORTABLE_DATA_SET ORDER BY PROJECT_NAME asc, PRWBSSEQUENCE asc;



  • 7.  Re:  SQL Query for Parent Task

    Posted 06-21-2017 11:02 AM

    Hello everyone,

     

    has anyone produced a query producing the inverse? I mean a Query for a list of all child tasks? I need to sum up values from custom fields associated to all child tasks.

     

    Many thanks,

    Olivier