ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Actuals reported in Tasks is different from what is logged in Timesheets

book

Article ID: 237743

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

It is noticed that Actual Hours shown in Tasks do not match with Actuals Hours reported in Timesheets.

Cause

Incorrect comparison of total Actual Hours with weekly Actual Hours from Timesheets.

Environment

Version: 15.9.3

Resolution

Use the following queries to compare Actual Hours in Tasks, with Actual Hours from Timesheets. Please note that data is derived from Timesheet tables here, and slices are not being used.

Total Actual Hours reported against individual Tasks can be obtained using the following query:

SELECT
    SUM(pr.practsum / 3600) hrs,
    prta.prname
FROM
    prtimesheet prt
    JOIN prtimeentry pr ON pr.prtimesheetid = prt.prid
    JOIN prassignment pra ON pr.prassignmentid = pra.prid
    JOIN prtask prta ON prta.prid = pra.prtaskid
    JOIN inv_investments ii ON ii.id = prta.prprojectid
    JOIN srm_resources sr ON prt.prresourceid = sr.id
    JOIN prtimeperiod pri ON pri.prid = prt.prtimeperiodid
WHERE
      upper(sr.unique_name) = '<Replace with Resource ID in Upper Case>'
    AND
      upper(ii.code) = '<Replace with Investment Code in Upper Case>'
GROUP BY
    prta.prname
ORDER BY SUM(pr.practsum / 3600) desc;

Actual Hours reported against Timesheets, with additional information about the periods:

SELECT
    pri.prstart,
    pri.prfinish,
    ii.name,
    sr.full_name,
    prt.prresourceid,
    pr.practsum / 3600 hrs,
    prta.prname
FROM
    prtimesheet prt
    JOIN prtimeentry pr ON pr.prtimesheetid = prt.prid
    JOIN prassignment pra ON pr.prassignmentid = pra.prid
    JOIN prtask prta ON prta.prid = pra.prtaskid
    JOIN inv_investments ii ON ii.id = prta.prprojectid
    JOIN srm_resources sr ON prt.prresourceid = sr.id
    JOIN prtimeperiod pri ON pri.prid = prt.prtimeperiodid
WHERE
      upper(sr.unique_name) = '<Replace with Resource ID in Upper Case>'
    AND
      upper(ii.code) = '<Replace with Investment Code in Upper Case>'
ORDER BY pri.prstart ASC;