Activity Spread Estimate Based on Median Activity Length
select
median(
greatest(nvl(target_end_date,to_date('12122000','mmddyyyy'))
,nvl(act_end_date,to_date('12122000','mmddyyyy'))
,nvl(reend_date,to_date('12122000','mmddyyyy'))
,nvl(rem_late_end_date,to_date('12122000','mmddyyyy')) )
-
least(nvl(target_start_date,to_date('12122199','mmddyyyy'))
,nvl(act_start_date,to_date('12122199','mmddyyyy'))
,nvl(restart_date,to_date('12122199','mmddyyyy'))
,nvl(rem_late_start_date,to_date('12122199','mmddyyyy')) )
) * count(*) Spread_Rows
from task t inner join project p on p.proj_id = t.proj_id and orig_proj_id is null and p.px_enable_publication_flag='Y'
where task_type in ('TT_Task','TT_Rsrc');
Resource Assignment Spread Estimate Based on Median Activity Length
select
median(
greatest(nvl(tr.target_end_date,to_date('12122000','mmddyyyy'))
,nvl(tr.act_end_date,to_date('12122000','mmddyyyy'))
,nvl(tr.reend_date,to_date('12122000','mmddyyyy'))
,nvl(tr.rem_late_end_date,to_date('12122000','mmddyyyy')) )
-
least(nvl(tr.target_start_date,to_date('12122199','mmddyyyy'))
,nvl(tr.act_start_date,to_date('12122199','mmddyyyy'))
,nvl(tr.restart_date,to_date('12122199','mmddyyyy'))
,nvl(tr.rem_late_start_date,to_date('12122199','mmddyyyy')) )
) * count(*) Spread_Rows
from taskrsrc tr inner join project p on p.proj_id = tr.proj_id and orig_proj_id is null and p.px_enable_publication_flag='Y'
inner join task t on t.task_id = tr.task_id
where task_type in ('TT_Task','TT_Rsrc');