AnsweredAssumed Answered

Sql Query for Allocation, Hard Allocation,Actual and ETC?

Question asked by giriraj.gupta1 on Jun 28, 2017
Latest reply on Jul 6, 2017 by YoannD

Hello all,

 

I am trying to make a single SQL query which gives me Monthly Allocation,Hard Allocation, Actual and ETC values for particular resource with Project code. I am able to fetch Data for Monthly Allocation,Hard Allocation, Actual and ETC individually but on joining not getting the expected data. It is doing cross join based on slice date. Please refer following queries and let me know if doing anything wrong here and how can i join so get expected data on monthly basis.

Like -

Resource | Project code | Month  | Allocation | Hard Allocation |  Actual | ETC

 A              | ABC                | Jan-17  | 10              | 8                           | 5           | 2

 

Allocation and Hard allocation - 

SELECT
--t.prResourceID RESOURCE_ID,
--t.prProjectID PROJECT_ID,
--t.prID TEAM_ID,
r.ID,
r.full_name,
inv.name Project_name,
inv.code code,
sr.slice_date MONTH_START_DATE,
SUM(ISNULL(CASE WHEN sr.request_name = 'MONTHLYRESOURCEALLOCCURVE' THEN sr.slice ELSE 0 END,0)) ALLOC_HOURS,
SUM(ISNULL(CASE WHEN sr.request_name = 'MONTHLYRESOURCEHARDALLOC' THEN sr.slice ELSE 0 END,0)) HARD_ALLOC_HOURS
FROM prTeam t
right outer join srm_resources r ON t.prResourceID = r.ID
full outer join INV_INVESTMENTS inv on inv.ID = t.PRPROJECTID
--left outer JOIN prj_blb_slices s ON t.prID = s.prj_object_id
left outer JOIN
(select sr1.id,sr1.request_name,s.prj_object_id,s.slice_date,s.slice from prj_blb_slicerequests sr1
inner join prj_blb_slices s ON s.slice_request_id = sr1.id
where sr1.request_name IN ('MONTHLYRESOURCEALLOCCURVE','MONTHLYRESOURCEHARDALLOC')) sr
ON t.prID = sr.prj_object_id
WHERE (r.resource_type <= 1)
GROUP BY inv.CODE, inv.name,r.id,r.full_name,sr.slice_date

 

Actual and ETC -

SELECT r.ID,
r.full_name,
inv.name,
inv.code,
--t.prProjectID PROJECT_ID,
--t.prID TASK_ID,
-- a.team_id TEAM_ID,
-- a.prID ASSIGNMENT_ID,
sra1.slice_date MONTH_START_DATE,
SUM(ISNULL(CASE WHEN sra1.request_name = 'MONTHLYRESOURCEACTCURVE' THEN sra1.slice ELSE 0 END,0)) ACTUAL_HOURS,
SUM(ISNULL(CASE WHEN sra1.request_name = 'MONTHLYRESOURCEESTCURVE' THEN sra1.slice ELSE 0 END,0)) ETC_HOURS
FROM PRASSIGNMENT a
INNER JOIN prTask t ON a.prTaskID = t.prID
INNER JOIN PRTEAM tm on a.TEAM_ID =tm.PRID
full outer JOIN inv_investments inv on tm.PRPROJECTID =inv.ID
right outer JOIN srm_resources r ON a.prResourceID = r.id
left outer join
(select sa.slice_date, sa.slice,sra.request_name,sa.PRJ_OBJECT_ID from prj_blb_slices sa INNER JOIN prj_blb_slicerequests sra
ON sa.slice_request_id = sra.id
where sra.request_name IN ('MONTHLYRESOURCEACTCURVE','MONTHLYRESOURCEESTCURVE'))sra1
ON a.prID = sra1.prj_object_id
WHERE
(r.resource_type <= 1)
GROUP BY r.id,r.full_name,inv.name,inv.code,t.prProjectID, t.prID, a.team_id, a.prID, sra1.slice_date

Outcomes