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.
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
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.
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
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.