SQL Query to Base Calendars

[Anon Anon]

HiCan anybody tell me what tables do I have to query to select the Non-Workday and Workday for a specific Calendar?Thank you

[Anon Anon]

Hi,  prcalendar is the master table for calendar.  This query will give list of base calendar in your system.                 select * from prcalendar where prname is not null.  The resources will be tagged to each calendar.The resource calendar is stored inprj_resources calendar.you can get that by using the query provided by siva at another post.  http://caforums.ca.com/ca/board/message?board.id=CAClarityGeneralDiscussion&message.id=162&query.id=7610#M162

The slices tables ( prj_blb_slicerequests ,prj_blb_slices ) hold the day / week /monthwise records for each individual resource,which is based on their availability that is dependent on their calendar.so if you take an resource which is tagged to that base calendar you can get the workday and non workday. Non workday will have zero hours(Mostly sat / sundays / public holidays(that should bemarked as non work day).Workday will have the available hours (8 or whatever defined in the base calendar)  select sum(data.slice) hrs into avail_hrs
      from       srm_resources s,
      (
              select             s.prj_object_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'
      ) data
      where s.id=data.prj_object_id       and s.unique_name = 'admin'
      and   slice_date between startdate and enddate
      group by s.unique_name  Pls have a look at an similar post ..http://caforums.ca.com/ca/board/message?board.id=CAClarityGeneralDiscussion&thread.id=4384  But to be honest i don't know how to relate the slices directly with the base calendar.....  cheers,sundar  

[Anon Anon]

Thank you sundarWhat I want is the Non-Workday and Workday for a specific Calendar not for any resource.If you go to Administration Tool -> Project Management -> Base Calendar -> Any Calendar then you can define Non-Workdays and Workdays. Those are the day I want to query.  

[Anon Anon]

The problem is that the calandar working days / non working days are stored in the PRVALUE "blob" against the PRCALENDAR table; so you are not going to be able to read that via SQL.  The solution above (and in the other thread) is just suggesting is that you you look at the allocation information for a resource that never-books "personal non-working days" (eg a technical system admin user).   The "availability" of such a user will match (exactly) the working days/non-working days of their calendar.  You still have problems with this though - as that availability is also stored in a "blob"; BUT this blob is crack-able - i.e. for the period of dates that you have set up for your timeslice configuration, then the data can be found (and read via SQL) in the relevant PRJ_BLB_SLICES record!      Not a simple answer though!     It might just be easier to "hard-code" your non-working day list off somewhere!  Dave. Message Edited by Dave on 13-10-2009 01:40 PM [left]

[Anon Anon]

Here is a trick I have used to get Non-Working Days.

1. Find a resource which is "support" or admin or some resource where no will touch the calendar.
2. Compare the slices for this resource to the other resources to find the differences.

Here is the query

SELECT stdcal.slice_date, stdcal.slice std_slice,
rescal.id id, rescal.slice res_slice,
rescal.prj_object_id res_id,
res.full_name res_name, res.manager_id

from

(SELECT * FROM sst094p.prj_blb_slices
WHERE slice_request_id = 1
AND prj_object_id = 5001054
AND slice_date BETWEEN @where:param:user_def:date:paramstartdate@ AND @where:param:user_def:date:paramenddate@) stdcal,

(SELECT * FROM sst094p.prj_blb_slices
WHERE slice_request_id = 1
AND slice_date BETWEEN @where:param:user_def:date:paramstartdate@ AND @where:param:user_def:date:paramenddate@) rescal,
sst094p.srm_resources res

WHERE
stdcal.slice_date = rescal.slice_date
AND stdcal.slice != rescal.slice
AND rescal.prj_object_id = res.id
AND rescal.slice = 0
AND res.is_active = 1