Clarity

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

Hello Giriraj,

Your sql query looks too complicated and you are not using the proper joins. Can you try my sql query as given below and see if it works for you?

select distinct d.name Project_Name, e.full_name Resource_Name,

(a.prAllocSum/3600) Allocation, (a.HARD_SUM/3600) Hard_Allocation,
(f.labor_actsum/3600) Actuals, (f.labor_etcsum/3600) ETC,

c.from_date From_Date, c.to_date To_Date
from prteam a, prj_blb_slices b, prj_blb_slicerequests c, srm_projects d, srm_resources e, inv_investments f
where a.prid = b.prj_object_id
and b.slice_request_id = c.id
and a.prprojectid = d.id
and a.prresourceid = e.id
and d.id = f.id
order by Project_Name;

Hope this helps!

Suhail.

Thanks Suhail for your feedback but this query is not giving me data on monthly basis that's my requirements and due to this i am using monthly slice request.

so if you are able to help me on this then it will be really great.

Hi Giriraj,

Please find the modified sql query as given below.

select distinct d.period_name Period, b.name Project_Name, e.full_name Resource_Name, sum(a.prallocsum/3600) Allocation, sum(a.hard_sum/3600) Hard_Allocation,
sum(b.labor_actsum/3600) Actuals, sum(b.labor_etcsum/3600) ETC
from prteam a, inv_investments b, fin_plans c, biz_com_periods d, srm_resources e
where a.prprojectid = b.id
and a.prresourceid = e.id
and b.odf_object_code = c.object_code
and c.start_period_id = d.id
and d.period_type = 'MONTHLY'
group by d.period_name, b.name, e.full_name
order by Period, Project_Name, Resource_Name ASC;

Hope this works for you on this occasion!!

Suhail.

Thanks Suhail but i am not getting any result on running this query. on just commenting the line 'and d.period_type = 'MONTHLY'' in query, it is running for a long time so not able to see any result. Have you run this query on you end? because in query, you are showing cost from Inv_investment table which is a sum so don't know it will give the cost on monthly basis for each resource.

Hello Giriraj,

I've helped you with the basic queries and tables to be used. I am not very sure what your requirements are, so, you will have to work further on this. And, yes I am getting values after running my above queries. Just make sure that the fields are not empty on the Bearing UI.

Suhail.

Thanks for your time and Help on this. My requirement is to get monthly data from DB like we get from team details tab for reporting purpose. I have made the query as in my initial post but not able to join to get all four attribute values together with no repetition of data. Any body else has any suggestions?

Giriraj,

My second query is giving the data that you require on a monthly basis for each resource belonging to the different projects on the CA Clarity PPM tool. You should not remove the period_type field from the query as that will classify that you need data on a monthly basis. If you remove it from the query, it will give you a lot of results including quarterly and yearly. That is the reason why it is taking a long time for the query to execute. Please refer the tables individually on the DB to understand the functionality of each of them. You can also refer to the Technical Reference Guide if you need any further help with the queries.

Suhail.

Suhail,

I got your query and aware of all the table used in it. The point that i want to highlight here is that prteam and inv_investments have a summation of cost. If we need to cost on monthly, quarterly or yearly basis then we need to Slices table (prj_blb_slices , prj_blb_slicerequests) to fetch the data. In your query, it will show the cost value same for all resources and for all months. you have joined resource and period table so it it show resource and periods but cost has not divided accordingly. I think Slices table (prj_blb_slices , prj_blb_slicerequests) should be used for that and value of Slice will be right value based on type of slice. like i have used -

SUM(ISNULL(CASE WHEN sra1.request_name = 'MONTHLYRESOURCEACTCURVE' THEN sra1.slice ELSE 0 END,0)) ACTUAL_HOURS

Guys, any suggestion on this query?

If you like to do this kind of query, generally I use the following structure :

Select   REF_DATE.V_DATE,
   D_ACT.VAL  ACTUALS,
   D_ETC.VAL ETC,[...]From   /* Generating a time reference table for one year on a mouthly basis */   (      SELECT ROUND(TO_DATE('01/01/2017','DD/MM/YYYY'),'MONTH') M_DATE,
             TO_CHAR(ROUND(TO_DATE('01/01/2017','DD/MM/YYYY'),'MONTH'),'YYYY-MM') V_DATE
      FROM DUAL
      UNION ALL
      SELECT ADD_MONTHS(ROUND(TO_DATE('01/01/2017','DD/MM/YYYY'),'MONTH'),1),
             TO_CHAR(ADD_MONTHS(ROUND(TO_DATE('01/01/2017','DD/MM/YYYY'),'MONTH'),1),'YYYY-MM')
      FROM DUAL
      UNION ALL
      SELECT ADD_MONTHS(ROUND(TO_DATE('01/01/2017','DD/MM/YYYY'),'MONTH'),2),
             TO_CHAR(ADD_MONTHS(ROUND(TO_DATE('01/01/2017','DD/MM/YYYY'),'MONTH'),2),'YYYY-MM')                 
      FROM DUAL
      UNION ALL
      SELECT ADD_MONTHS(ROUND(TO_DATE('01/01/2017','DD/MM/YYYY'),'MONTH'),3),
             TO_CHAR(ADD_MONTHS(ROUND(TO_DATE('01/01/2017','DD/MM/YYYY'),'MONTH'),3),'YYYY-MM')                 
      FROM DUAL
     
      [.....]

) REF_DATELEFT OUTER JOIN(   /* Here put the query to extract your measure like ETC, ACT, HARD Aloc ... */   select
            SL.SLICE_DATE,
            SUM(NVL(SL.SLICE,0))    VAL
        from
            INV_INVESTMENTS I
                INNER JOIN PRTASK T ON (T.prProjectID = I.ID)
                    INNER JOIN PRASSIGNMENT A ON (A.prTaskID = T.prID)
                        INNER JOIN PRJ_BLB_SLICES SL ON (SL.PRJ_OBJECT_ID =  A.prID AND SL.SLICE_REQUEST_ID = 5)        
                    INNER JOIN SRM_RESOURCES R ON (A.prResourceId = R.ID)
        where
        and I.ODF_OBJECT_CODE = 'project'
        and I.IS_ACTIVE = 1
        and R.IS_ACTIVE = 1
        group by
            SL.SLICE_DATE,
)  D_ACT ON (D_ACT.SLICE_DATE = REF_DATE.M_DATE

LEFT OUTER JOIN

(

/* Here put the query to extract your measure like ETC, ACT, HARD Aloc ... */

select
            SL.SLICE_DATE,
            SUM(NVL(SL.SLICE,0))    VAL
        from
            INV_INVESTMENTS I
                INNER JOIN PRTASK T ON (T.prProjectID = I.ID)
                    INNER JOIN PRASSIGNMENT A ON (A.prTaskID = T.prID)
                        INNER JOIN PRJ_BLB_SLICES SL ON (SL.PRJ_OBJECT_ID =  A.prID AND SL.SLICE_REQUEST_ID = 4)        
                    INNER JOIN SRM_RESOURCES R ON (A.prResourceId = R.ID)
        where
        and I.ODF_OBJECT_CODE = 'project'
        and I.IS_ACTIVE = 1
        and R.IS_ACTIVE = 1
        group by
            SL.SLICE_DATE,
)  D_ETC ON (D_ACT.SLICE_DATE = REF_DATE.M_DATE)

 [....]

Please note that the preceding example does not include any information regarding project or resource, you can add it as CROSS join to create an other REF_* data set like all project or resource that are eligible for the query...

I hope that will help you