Reading company holidays from base calendars

[Mangesh Gohad]

Hi All,

I want to read all company holidays marked in a particular base calendar through SQL query.

I am able to read it from individual resource calendars, but I want to read it from base calendars, as there is no way to distinguish company holidays from resource PTO in resource calendars through SQL.

I guess, this data is stored in column PRVALUE (which holds a BLOB value) in PRCALENDAR. However, I am not sure which slice does this data get stored into.

Any help in this regard is greatly appreacited.

[Anon Anon]

Yes its a BLOB field - you are not going to be able to read that directly in SQL.

mangeshgohad wrote:

I am able to read it from individual resource calendars

How are you doing that? Via a read of the resource's AVAILABILITY slice (this being a combination of base calendar and personal calendar)?

--

Perhaps create a dummy resource who inherits the base calendar (but does not book any personal holidays of course) and then you can SQL read the data from the availability slice associated with that resource?

[Deactivated User]

I don't think there is a way to read the base calendars directly, unless you have the blobcracker available.
The normal way would be to read the admin availability as the admin is not expected to have any personal holidays..

If you need to read more calendars you could read which resource is attached to those and take such a resource and rad it availability from the slices..
Once you create personal exceptions for a resource then the resource is not associated with a base calendar any more, but with a personal calendar.

Martti K.

[Mangesh Gohad]

Dave: Yes, I am reading resource availability.

It seems that a workaround is the only way out :sad. Creating dummy users for each base calendar is a good idea. Thanks!

Thank you all for your suggestions! :)

[Deactivated User]

I thought I already commented that work around, but I don't see that anywhere, maybe it was censored.

Are you saying that every single resource in your system has personal exceptions and no one is associated with any of the base calendars?
Neither do you have any resources in Clarity whose users have left your organization?
Even if a dummy resource does not consume a license it still creates a user record.

My proposal is to work something like

SELECT
RES.PRNAME Calendar_name
,PRJ_BLB_SLICES.SLICE_DATE
,PRJ_BLB_SLICES.SLICE
,RES.LAST_NAME
,RES.FIRST_NAME
,RES.UNIQUE_NAME
FROM
PRJ_BLB_SLICES
,PRJ_BLB_SLICEREQUESTS
,
(SELECT top 1
SRM_RESOURCES.ID
,SRM_RESOURCES.LAST_NAME
,SRM_RESOURCES.FIRST_NAME
,SRM_RESOURCES.UNIQUE_NAME
,PRCALENDAR.PRID
,PRCALENDAR.PRNAME
FROM
SRM_RESOURCES
,PRJ_RESOURCES
,PRCALENDAR

WHERE

SRM_RESOURCES.ID = PRJ_RESOURCES.PRID
AND PRJ_RESOURCES.PRCALENDARID = PRCALENDAR.PRID
AND PRCALENDAR.PRRESOURCEID is NULL) RES
WHERE
PRJ_BLB_SLICEREQUESTS.ID =PRJ_BLB_SLICES.SLICE_REQUEST_ID
AND PRJ_BLB_SLICEREQUESTS.REQUEST_NAME = 'DAILYRESOURCEAVAILCURVE'
AND PRJ_BLB_SLICES.PRJ_OBJECT_ID = RES.ID
ORDER By RES.PRNAME, PRJ_BLB_SLICES.SLICE_DATE

Martti K.

[Anon Anon]

Yes that's true there is no way out. We use a very crude :sad:wacko:method to do this.

Store the holidays in a custom table and MINUS out those days from the Workdays while calculating Monthly FTEs.