Clarity

  • 1.  Query (or NSQL) for tasks, from a template, that have never had time logged/zero actuals

    Posted Mar 06, 2015 12:01 PM

    I have tried several queries and no matter what I do, I keep getting hung up on tasks within a project or by resource . (the results show me for a specific project/resource or task)


    here is what I need


    Show all tasks (preferably within a specific OBS or I can use the prexternal id ranges) where no one has ever logged time to it.

    Basically, we have a template used to create the projects

    we need to evaluate if there are tasks that have never been used for time entry across all projects. I just need the task name and id..

    Then we can evaluate whether we need to keep those tasks in the template or not.

    A query is fine, but if someone has the nsql for this, that will work too - I just need to run it one time essentially.

    I don't see a way to get this ootb even with the content package.

    I3.3.

    on premise

    SQL

    we don't use BOBJ either, so running this against the db is my best route


    Thanks




  • 2.  Re: Query (or NSQL) for tasks, from a template, that have never had time logged/zero actuals

    Posted Mar 06, 2015 06:20 PM

    Do you have some SQL prototype to share?

     

    If I understand the problem, this query may help you:

     

    select

      tsk.prid

    from

      prtask tsk

    where

      tsk.prid not in (

      select

      ***.prtaskid

      from

      prassignment ***

      join srm_resources res_worker on ***.prresourceid = res_worker.id

      join prtimeentry tmentry

      join prtimesheet prtimesht on prtimesht.prid = tmentry.prtimesheetid

      on tmentry.prassignmentid = ***.prid

      where

      nvl(tmentry.practsum, 0) > 0

      )

     

    It returns all  task ids that are not linked to a timesheet entry where actuals are bigger than zero.

     

    ps: if you will use it, check before. I do not test it!



  • 3.  Re: Query (or NSQL) for tasks, from a template, that have never had time logged/zero actuals

    Posted Apr 23, 2015 10:35 AM

    I am so sorry.. I never noticed this had a reply and I never got an email

    Anyway, thank you, but that did not work. I was getting syntax errors and no I don't have a prototype.. wasn't quite sure where to start.

    However, I thought it might be easier to just search all task ids (where prexternalid  .like '%***%")l but have no assignments.   .  Any idea on how to tweak it? I am not a developer:) so I struggle with switching things around in SQL I know I need to join prassignment and prtask and I would constrain on the task id  but it is that "does not exist" that trips me up

     

    thanks



  • 4.  Re: Query (or NSQL) for tasks, from a template, that have never had time logged/zero actuals
    Best Answer

    Posted Apr 23, 2015 11:48 AM

    I think this will do it for you. Tested in Oracle. Quick scan of my results looked plausible. It ignores timesheet status (technically speaking, it could return a task which once had time booked against it but was subsequently zero-ed out by an adjustment).

     

    SELECT    UNIQUETASKS.*
    FROM      (
              SELECT DISTINCT   PT.PREXTERNALID TASK_ID,
                                PT.PRNAME TASK_NAME
              FROM              PRTASK PT
              WHERE     PRISTASK = 1
              ) UNIQUETASKS LEFT OUTER JOIN
              (
              /*Get all tasks by prexternalid and name with 1 or more assignments in prtimeentry*/
              SELECT DISTINCT   PT.PREXTERNALID TASK_ID,
                                PT.PRNAME TASK_NAME
              FROM              PRTASK PT INNER JOIN
                                PRASSIGNMENT PA ON
                                PT.PRID = PA.PRTASKID INNER JOIN
                                PRTIMEENTRY PTE ON
                                PA.PRID = PTE.PRASSIGNMENTID 
              WHERE             PTE.PRACTSUM > 0
              ) TE ON
              NVL(UNIQUETASKS.TASK_ID, 'NO-ID') = NVL(TE.TASK_ID, 'NO-ID') AND
              UNIQUETASKS.TASK_NAME = TE.TASK_NAME
    WHERE     TE.TASK_NAME IS NULL    
    
    


  • 5.  Re: Query (or NSQL) for tasks, from a template, that have never had time logged/zero actuals

    Posted Jun 20, 2015 02:49 AM

    vtleogal2, Lynn if you are still looking at it and want to combine  the templates used to tasks not logged time against have a look at

    CA PPM Tuesday Tip: Which template was a project created or copied from?



  • 6.  Re: Query (or NSQL) for tasks, from a template, that have never had time logged/zero actuals

    Posted Jun 22, 2015 08:06 AM

    Thanks Andrew-- once again, I never got a notice on this reply, but I did for Urmas  on Jun 20

    Anyway, I ran your query , changed if for SQL and constrained on the task IDs desired from the specific template and it worked. Thanks! I will work on extending it to get it joined to the show active projects only.

    I appreciate it and sorry so late

     

    Urmas - I saw that article, but that does not help me in this specific request. Thanks though