Help with Query

[Anon Anon]

I am trying to figure out a way to pull current ETC and Baseline Usage(current baseline) by Assignment. The desired output being a list that shows project name & ID, Project Role, Employment Type, current ETC, and others. I've hit a bit of a wall, which may just be because of staring at it too long, but when I try to pull the ETC it always gives me the baseline ETC. Is there any simplified way to get the current ETC on assignment?

 

Here is my current query:

 

SELECT   @SELECT:DIM:USER_DEF:IMPLIED:PRJ:i.id || '-' || b.base_id || '-' || rol.id || '-' || r.hr_id || '-' || r.person_type:dimid@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:i.id:prj_db_id@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:i.name:prj_name@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:i.code:prj_id@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:rol.full_name:prj_role@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:stg.name:stage@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:b.base_name:rev_name@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:b.base_code:rev_code@,

         @SELECT:DIM_PROP:USER_DEF:BOOLEAN:PRJ:b.is_current:cur_revision@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:b.base_desc:rev_description@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:b.created_date:rev_created_date@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:r.hr_id:res_hrid@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:CASE WHEN r.hr_id IS NULL THEN NULL ELSE r.id END:res_db_id@,

         @SELECT:DIM_PROP:USER_DEF:IMPLIED:PRJ:r.emp_type:emp_type@,

         @SELECT:METRIC:USER_DEF:IMPLIED:SUM(a.prestsum)/3600:prj_prestsum:AGG@,

         @SELECT:METRIC:USER_DEF:IMPLIED:SUM(b.hrs)/3600:etc:AGG@,

         @SELECT:METRIC:USER_DEF:IMPLIED:NVL(SUM(b.hrs)/3600, 0) + NVL(SUM(a.practsum)/3600, 0):eac:AGG@

 

 

FROM inv_investments i

JOIN inv_projects ip ON ip.prid = i.id AND ip.is_template = 0 AND ip.is_program = 0

LEFT JOIN prtask t ON t.prprojectid = i.id

LEFT JOIN prassignment a ON a.prtaskid = t.prid

LEFT JOIN prteam tm ON tm.prid = a.team_id

LEFT JOIN srm_resources rol ON rol.id = tm.prroleid

LEFT JOIN (SELECT b.id base_id, bd.object_id, bd.usage_sum hrs, b.name base_name

                , b.code base_code, b.is_current , b.description base_desc, b.created_date

           FROM prj_baseline_details bd

           JOIN prj_baselines b ON b.id = bd.baseline_id

           WHERE bd.object_type = 'ASSIGNMENT') b

      ON b.object_id = a.prid

LEFT JOIN (SELECT r.id, r.person_type, pr.prisrole, ocr.hr_id, et.name emp_type

           FROM srm_resources r

           LEFT JOIN prj_resources pr ON pr.prid = r.id --AND pr.prisrole = 0

           LEFT JOIN odf_ca_resource ocr ON ocr.id = r.id

           LEFT JOIN cmn_lookups_v et ON et.id = r.person_type AND et.language_code = 'en' AND et.lookup_type = 'SRM_RESOURCE_TYPE'         

          ) r ON r.id = a.prresourceid

LEFT JOIN cmn_lookups_v stg ON stg.lookup_code = i.stage_code AND stg.language_code = 'en' AND stg.lookup_type = 'INV_STAGE_TYPE'

WHERE @FILTER@

AND   @WHERE:PARAM:USER_DEF:INTEGER:i.id:prj_id@

AND   @WHERE:PARAM:USER_DEF:INTEGER:i.is_Active:prj_active@

AND   (r.prisrole IS NULL OR @WHERE:PARAM:USER_DEF:INTEGER:r.prisrole:isrole@)

AND   (r.person_type IS NULL OR @WHERE:PARAM:USER_DEF:INTEGER:r.person_type:res_person_type@)

GROUP BY i.id || '-' || b.base_id || '-' || rol.id || '-' || r.hr_id || '-' || r.person_type

     , i.id, i.name, i.code, rol.full_name, stg.name

     , b.base_name, b.base_code, b.is_current

     , b.base_desc, b.created_date

     , r.hr_id, r.emp_type, CASE WHEN r.hr_id IS NULL THEN NULL ELSE r.id END

HAVING @HAVING_FILTER@

[Deactivated User]

Current ETC is

SUM(a.prestsum)/3600:prj_prestsum:AGG

baseline (ETC plus Act) is

SUM(b.hrs)/3600:etc:AGG

[Anon Anon]

From my analysis of the query in a portlet and comparing data:

• prj_prestsum - shows the ETC at baseline
• hrs - shows the baseline usage at baseline

[Deactivated User]

Might I suggest you do it again with a single project and compare only

SUM(a.prestsum)/3600 to the total estimate

SUM(b.hrs)/3600 to the total baseline

as they are displayed in the GUI

and post your queries here

 

Even though the documentations covers the use of metrics I normally do not define metrics just properties.

When I do that I normally have different label for each property and not like you have the same label for all metrics (AGG)

[Anon Anon]

I'm not sure I understand what you're asking. If I don't change the query except to only select those two values which are already being selected then how could it be different?

 

Looking into the issue over the holidays; would it be a better idea to use a UNION to get the current ETC?

 

I apologize in advance for what is likely silly questions, but I'm very new to Clarity and not an overly accomplished SQL user.

[Deactivated User]

This is to illustrate that baseline is actual or ETC which ever there is. For simplicity it is only one task an resource.

This is the Actuals, ETC and baseline in the project.

Using query

Select inv_investments.id, inv_investments.name, practsum/3600, prestsum/3600 from prassignment, prtask, inv_investments

where

inv_investments.id = 5023055

and prassignment.prtaskid = prtask.prid

and prtask.prprojectid = inv_investments.id

 

gives

illustrates how you verify the actuals and ETC displayed in the GUI.

 

Using query

SELECT prj_baselines.id base_id,

prj_baseline_details.object_id,

prj_baseline_details.usage_sum/3600 hrs,

prj_baselines.name base_name,

prj_baselines.code base_code,

prj_baselines.is_current ,

prj_baselines.description base_desc,

prj_baselines.created_date

FROM prassignment, prtask, inv_investments, prj_baseline_details,

            prj_baselines

WHERE  prj_baselines.id = prj_baseline_details.baseline_id

           and prj_baseline_details.object_type = 'ASSIGNMENT'

and prassignment.prid =  prj_baseline_details.OBJECT_ID

and inv_investments.id = 5023055

and prassignment.prtaskid = prtask.prid

and prtask.prprojectid = inv_investments.id

gives

Illustrates how you verify the baseline hours displayed in the GUI.

 

So

Current ETC is

SUM(a.prestsum)/3600:prj_prestsum:AGG

baseline (ETC plus Act) is

SUM(b.hrs)/3600:etc:AGG

 

Not

• prj_prestsum - shows the ETC at baseline
• hrs - shows the baseline usage at baseline

[Anon Anon]

Thank you for your help. This makes a lot more sense now.