Clarity PPM

Expand all | Collapse all

sql query -pull data where resource allocated less than 5 days  for a given project in a current year

Jump to Best Answer
  • 1.  sql query -pull data where resource allocated less than 5 days  for a given project in a current year

    Posted 10-30-2018 10:22 PM

    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



  • 2.  Re: sql query -pull data where resource allocated less than 5 days  for a given project in a current year
    Best Answer

    Posted 10-31-2018 09:37 AM

    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.



  • 3.  Re: sql query -pull data where resource allocated less than 5 days  for a given project in a current year

    Posted 11-01-2018 09:46 PM

    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