Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Working days in month of the resources

Highlighted
Ramazan1
Regular Collector

Working days in month of the resources

Hello,

 

I need to write a query, to find out how many days an employee works in a month. That means, e.g. in May we have 4 holidays in Austria (we use Austian calender) and statistical absences, which are saved in the calender of each resource.

 

May 23 days (without Saturday and Sunday) - 4 Holidays - 3 days statistical absence = 16 working days.

 

At the end the query should show me the rows:

OrgUnit | Employees | Month (!better week!)    | Workingdays

ITAB       | 23                | May (better week = 21) | 23 * 16 = 368 days (or in weeks, e.g. no holidays no absences in that week => 23*5 = 115 days)

 

I am not so good in SQL and need your support. Actually the problem appears to find out the month and calculating the workingdays.

 

Regards,

Ramazan

5 REPLIES
Erik Cole
Honored Contributor

Re: Working days in month of the resources

What is "3 days statistical absence"? Is it the same for each employee? Each month?

 

Here's a quick query for working days in a month, given the regional calendar id of the company calendar:

 

select to_char(working_day_date,'mm') "MONTH",count(*) "DAYS"
from kdrv_working_days
where calendar_id = 20000
and working_day_date between to_date('1/1/2013','mm/dd/yyyy') and to_date('12/31/2013','mm/dd/yyyy')
and working_day_flag = 'Y'
group by to_char(working_day_date,'mm')
order by 1

Ramazan1
Regular Collector

Re: Working days in month of the resources

Here is a screenshot to make it clear. In the Screenshot you see the months May and June

 

May has 3 holidays and 3 Vacations (Statistical Absence),

that means: May has 23 days (without Saturday and Sunday) - 4 holidays - 3 Vacations = 16 Working days

 

June has no holidays but 5 Vacations (Statistical Absence)

that means: June has 20 days (without Saturday and Sunday) - 5 Vacations = 15 Working days

 

the issue is: We have 6 departments (ITA, ITB, ITG, ITH, ITO, ITS). Only the department ITA works with the statistical absences. The other 5 departments are working with normal austrian calender.

 

Actually we don't use the PPM Calendar for vacations. we have another tool for this. But to have a feeling how much days is a user available in the months we put the statistical absences.

 

Maybe this would be an idea. I am in 3 resource pools (see screenshot resourcepools.png) and calculate there the availability of the users.

 

Regards,

Ramazan

Erik Cole
Honored Contributor

Re: Working days in month of the resources

I guess it depends on what your result set needs to look like. If you can live with a count of the total FTE by pool and role per month then PPM has already done the heavy lifting for you:

 

select rp.resource_pool_name,to_char(p.start_date,'mm') "MONTH_NUM", rr.role_name,(sum(total_fte)/100) "TOTAL_FTE"
from krsc_rsc_pool_period_sum s
join ppm_fiscal_periods p on p.fiscal_period_id = s.period_id
join rsc_resource_pools rp on rp.resource_pool_id = s.resource_pool_id
join rsc_roles rr on rr.role_id = s.role_id
where p.long_name like '%2013'
group by rp.resource_pool_name,rr.role_name,to_char(p.start_date,'mm')
order by 1,2,3

 

For the resources with statistical absences, you might want to just reduce their pool capacity accordingly.

Ramazan1
Regular Collector

Re: Working days in month of the resources

Hello Erik,

 

this is very fine. But I need it for the OrgUnits to say that OrgUnit has 24 PDs in that month, and so on. Maybe when I could get the availability in the resource pools, e.g. RP1 = 8,2 days, RP2  = 5 days and RP3 = 1,8 days. Then I could sum the values.

User A = 8,2 + 5 + 4 = 12 days -> User A belongs to OrgUnit ITAX, and when I have 10 users in ITAX where each User has 12 days in e.g. May, the result will look like

 

OrgUit |  PDs

ITAX     |   120 days

 

this is what I need

 

Please look at the screenshot.

 

Regards,

Ramazan

Erik Cole
Honored Contributor

Re: Working days in month of the resources

If you don't need to get fancy about resources shifting pools in different months, and you're only interested in the total number of resources that belong to an org unit, you can get that from krsc_org_unit_members_v (think that's it). Then just add your working days from the SQL above.

//Add this to "OnDomLoad" event