Clarity

Hi,

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]

i tried...

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 ?

thanks#

suhail-sayed

suhailsayed

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, 

Thanks David.

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.slice
having sum (prjblbs.slice) < (dh.slice) * 5

Thanks