Clarity

  • 1.  SQL statement to retrieve the availability field of a resource

    Posted Dec 13, 2015 03:31 AM

    Hello,

     

    I am looking for the query that is required to retrieve the value that is called "Availability" located on the Resource object. It's a calculated field but I like to pull it in a portlet that allows us to get a simple overview of All resources and their default availability. so, something like this. In the end I want to calculate the FTE per person

     

    Resource (Jan Jansen) - 8.0 hrs availability.

     

    thanks

     

    Joost



  • 2.  Re: SQL statement to retrieve the availability field of a resource

    Posted Dec 13, 2015 06:07 AM

    There are queries in

    SQL Query to Base Calendars

    which illustrate how you would get to the data.

     

    Somebody has also a query for availlability per day

     

    select

    u.last_name

    ,u.first_name

    ,u.unique_name

    --,s.prj_object_id

    ,s.slice_date

    ,s.slice

    from

    prj_blb_slicerequests r

    ,prj_blb_slices s

    ,srm_resources u

    where

    r.id = s.slice_request_id

    and r.request_name = 'DAILYRESOURCEAVAILCURVE'

    and u.id =  s.prj_object_id

    and u.unique_name = 'cal' 

    and slice_date >= '2013-04-01'

    and slice_date <= '2013-04-15'



  • 3.  Re: SQL statement to retrieve the availability field of a resource

    Posted Dec 13, 2015 06:42 AM

    Hi, thanks for this but I don't think this is what I am looking for as  this gives me the availability of a certain period, but I want to retrieve the default value which is visible on the resource it self?

     



  • 4.  Re: SQL statement to retrieve the availability field of a resource

    Posted Dec 13, 2015 07:37 AM

    Sorry to say that is the closest you can get.

    The availability rate field in the resource properties is not a database field, but a virtual field which is calculated on the fly.

     

    For recent discussion touching that see

    Availability Rate



  • 5.  Re: SQL statement to retrieve the availability field of a resource

    Posted Dec 13, 2015 07:42 AM

    I hope in PRCALENDAR table HOURS_PER_DAY column have the resource availability.

     

    For Ex:

    SELECT HOURS_PER_DAY FROM PRCALENDAR WHERE PRRESOURCEID=****** (PRRESOURCEID nothing but SRM_RESOURCES.ID)

     

    Thanks.



  • 6.  Re: SQL statement to retrieve the availability field of a resource

    Broadcom Employee
    Posted Dec 13, 2015 09:33 AM

    Hi Joost,

     

    I hope this technical document will help you

     

    http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec614153.aspx

     

    Regards

    Suman



  • 7.  Re: SQL statement to retrieve the availability field of a resource

    Posted Dec 13, 2015 01:05 PM

    hi Suman, thanks this helps. but how do I determine which date is equal to the availability rate? Can I just pick one but will that be always equal for everyone? or should i calculate the next working day? and how can I do that?

     

    thanks



  • 8.  Re: SQL statement to retrieve the availability field of a resource

    Posted Dec 13, 2015 01:16 PM

    Hi,

     

    I think I have it now:

     

    SELECT

         AVG(s.Slice) qty

         FROM

         prj_blb_slices s,

         srm_resources r

         WHERE

         s.prj_object_id = r.id and

         r.unique_name like 'XXXX' and

         slice_request_id = 1 and

         slice_date >= sysdate and

         slice_date < sysdate + 30 and

         s.slice > 0

     

    So I am looking at the average working time of a specific resource from today until today + 30 days. Would this work?

     

    Joost



  • 9.  Re: SQL statement to retrieve the availability field of a resource

    Posted Dec 13, 2015 02:14 PM

    You query would return exactly that. Go ahead.

     

    The thing is that the daily availability will change when either the shifts in the base calendar are changed or resource calendar are changed. How often are they in you system?

    In many cases the calendar is selected and the shifts are set when the resource is created and then they are not changed ever after.