Clarity

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.

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!)

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}

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 t.prid = {:param_child_task}

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

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;

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