Project and Portfolio Management Practitioners Forum
cancel

How to find scheduled time of a task for a month

Highlighted
riznad
Super Contributor.

How to find scheduled time of a task for a month

Hello All;

I want to find scheduled time for a task for a month.

 

Suppose Task A scheduled starts on 20.04.2012 and scheduled finishes 10.05.2012 and its scheduled duration 10 business days.

 

I want to find scheduled duration for the month April.

 

The compare work plan button on staffing profile shows the value but I could not find the formula in any document or in ay function on database.

 

The formula for the example is (10 business days / total business days between 20.04.2012 and 10.05.2012 ) * business days between 20.04.2012 and 30.04.2012

 

I need to find general rule.

Could you help me.

regards

3 REPLIES
riznad
Super Contributor.

Re: How to find scheduled time of a task for a month

Here is my query.

I want to add a coloumn for the month a coulmn for scheduled efford.

 

select p.pfm_request_id as project_no, pwp.work_plan_id, wta.actuals_id,wt.task_id ,wti.name, wts.sched_effort as task_sched_effort, wta.act_effort as task_act_effort, ku.full_name, wwu.sched_effort as resource_sched_effort, wwu.act_effort as resource_act_effort from PPMTFKB_USR.PM_WORK_PLANS pwp, PPMTFKB_USR.PM_PROJECTS p, PPMTFKB_USR.WP_TASKS wt, PPMTFKB_USR.WP_TASK_ACTUALS wta, PPMTFKB_USR.WP_TASK_SCHEDULE wts, PPMTFKB_USR.WP_TASK_INFO wti, PPMTFKB_USR.wp_work_units wwu, PPMTFKB_USR.knta_users ku where p.project_id=pwp.project_id and wts.task_schedule_id=wta.actuals_id and entity_type='WORK_PLAN' and wt.work_plan_id=pwp.work_plan_id and wta.actuals_id=wt.task_actuals_id and wti.task_info_id=wta.actuals_id and wt.task_id = wwu.task_id(+) and ku.user_id(+)= wwu.resource_id order by p.pfm_request_id, wt.task_id

Niraj Prabhu
Outstanding Contributor.

Re: How to find scheduled time of a task for a month

you can use the timsheets table with TM_ACTUAL_EFFORT giving you EFFORT_ORDER join with COMPONENT_SEQ of ITG_COMPONENTS table, then to_char(start_time, 'Mon-YYYY') should give you what you need.

start_time is in ITG_COMPONENTS table
Niraj P.
riznad
Super Contributor.

Re: How to find scheduled time of a task for a month

Hi Niraj;

I joined two tables.

 

select tae.*, IC.* from PPMTFKB_USR.TM_ACTUALS_EFFORT tae, PPMTFKB_USR.ITG_COMPONENTS IC
where tae.effort_order=IC.component_seq
and to_char(IC.start_time, 'MM-YYYY')='04-2012'

 

which coloumn gives scheduled time. I need to sum that coloumn.

Regards