Hi Ben.
For
a task, PRTASK.PRDURATION is Business Workday Calendar cognizant. For a collection of tasks (with potential gaps or overlap) this gets a little trickier. Here's an old trick as well as a 12.1 twist on an old trick. I'm curious to hear other (easier? more robust?) approaches/tricks for this.
Question to Answer: Based upon our business' work calendar in Clarity, how many business days are between dates X and Y?
Starting Assumptions:
1) Your System has an Admin user whose User ID is '1'
SELECT * FROM SRM_RESOURCES
WHERE ID = 1
2) The Admin's Base Calendar is set to your organization's Workday Calendar (the calendar is current too :grin:).
3) Admin has an Availability of 8.
4) You know the start and finish dates you're testing for.
5) You're staying within your slices.
6) No one is monkeying with/making calendar exceptions for the Admin user.
Basically we use the Admin user's availability from the time slices to tell us the 'legal' workdays. If the above assumptions are true, here are the workdays between Jan 1 2011 & April 1st 2011:
SELECT (SUM(slice_data.slice)/8) AS business_days
FROM
(SELECT s.prj_object_id AS resource_id
, s.slice_date
, s.slice
FROM
prj_blb_slicerequests r
, prj_blb_slices s
WHERE r.id = s.slice_request_id
AND r.request_name = 'DAILYRESOURCEAVAILCURVE') slice_data
WHERE
slice_data.resource_id = 1
AND slice_data.slice_date
BETWEEN '1/01/2011' and '4/01/2011'
If you have 12.1, get to know the new report tables and views! These are all prefaced with 'RPT'.
Here's the same code using a 'new in 12.1' aggregate reporting view:
SELECT SUM(AVAIL_FTE) AS business_days
FROM RPT_RES_D_AVAIL_FTE_V
WHERE RESOURCE_ID = 1
AND DAILY_START_DATE BETWEEN '1/01/2011' and '4/01/2011'
How to test this (in your dev system of course):
1) Make sure the Admin user is setup against your Business' Calender.
2) Run
Time Slicing.
3) Once complete, run either of the above queries and log the results.
4) Goto your Business' Workday Calendar in Clarity and make a couple workday exceptions between the constraining dates in the above WHERE clause.
5) Run
Time Slicing.... go grab some lunch. This one's gonna take awhile (you've just changed the global calendar - this'll need to replicate to all user slices associated with your Calendar). Once complete...
6) Run the above queries again and see if the results match your expectations.
Traps to watch for:
1) Above examples are from the Dailys. The range you're testing for must be within the range of your slices (Dailys, Weekly, Monthlys, etc).
2) Because this is slice based and slices roll, anything built using this approach is anchored real time at the time of viewing. You will not be able to datamine back in time past the limit of your slices. We use this approach for many "Business Days" or "Max Availability" between <Today> and <Deliverable or Milestone X>. Works great for near term metrics like this, might not be an appropriate solution to your 'historic performance measurement' problem.
3) Careful with the 'BETWEEN'. Round up and down as appropriate.
4)
Careful with FINISH dates in Clarity.
Use COP_CALC_FINISH_FCT function as appropriate.
HTH,
-R