I I am trying to write a query that will pull the details of resource who is allocated in a project less than 5 days. let suppose he is allocated to 3 projects. And in 1 project - he was just for 4 days. there we would be needing only the details related to that project information where he allocated to 4 not other projects.[valid to for current year only]
select distinct srm_prj.name Project_Name, srm.full_name Resource_Name, prjbs_r.from_date From_Date, prjbs_r.to_date To_Date from prteam pr, prj_blb_slices prjblbs, prj_blb_slicerequests prjbs_r, srm_projects srm_prj, srm_resources srm, inv_investments inv where pr.prid = prjblbs.prj_object_id and prjblbs.slice_request_id = prjbs_r.id and prjblbs.slice_request_id = 5001001 and to_char(prjbs_r.from_date, 'YYYY') = to_char(sysdate, 'YYYY') and pr.prprojectid = srm_prj.id and pr.prresourceid = srm.id and srm_prj.id = inv.id
-- and srm_prj.id = ''
After this, i am not sure how to implement that logic of allocation of project is lesser than 5.
someone can help me in this ?
This line looks wrong;
and to_char(prjbs_r.from_date, 'YYYY') = to_char(sysdate, 'YYYY')
(The date range you want to pick up is on the prj_blb_slices not the prj_blb_slicerequests)
You need to check that the slice #5001001 is set up right too (needs to be a yearly allocation slice I would think)
And then you want to check that the SUM(prj_blb_slices.slice) is less than 5 days - I think the slice is held in hours though.
you are right,
I have prepared query for this. Works as expected.
with daily_hours as (select Slice, resource_id from (select cal.prid calendar_id, SLC.slice Slice, res.prid resource_id, rank() over(partition by res.prid order by SLC.slice desc) rnk from prcalendar cal INNER JOIN prj_resources res on res.prcalendarid = cal.prid INNER JOIN SRM_RESOURCES SRM ON SRM.ID = res.prid INNER JOIN PRJ_BLB_SLICES SLC ON SLC.prj_object_id = res.prid INNER JOIN PRJ_BLB_SLICEREQUESTS SLRQST ON SLC.SLICE_REQUEST_ID = SLRQST.ID and SLRQST.id = ' ' --Daily Resource Availibility group by cal.prid, SLC.slice, res.prid) where rnk = 1)select srm_prj.name Project_Name, srm.full_name Resource_Name, sum(prjblbs.slice) Total_Working from prteam pr INNER JOIN prj_blb_slices prjblbs on pr.prid = prjblbs.prj_object_id INNER JOIN prj_blb_slicerequests prjbs_r on prjblbs.slice_request_id = prjbs_r.id and prjblbs.slice_request_id = ' ' --QUARTERLYRESOURCEALLOCCURVE and to_char(prjblbs.slice_date, 'YYYY') = to_char(sysdate, 'YYYY') -- fetching for current year INNER JOIN srm_projects srm_prj on pr.prprojectid = srm_prj.id INNER JOIN srm_resources srm on pr.prresourceid = srm.id INNER JOIN daily_hours dh on dh.resource_id = srm.id INNER JOIN inv_investments inv on srm_prj.id = inv.id and srm_prj.id = ' ' -- project id here group by srm_prj.name, srm.full_name, dh.slicehaving sum (prjblbs.slice) < (dh.slice) * 5