AnsweredAssumed Answered

Help with NSQL Query

Question asked by Tammi Champion on Apr 8, 2010
Latest reply on Jun 8, 2010 by Tammi
Good Morning Everyone,Can someone please help me with my NSQL query, as opposed to adding the ETC and ACT to my portlet I would like to see the TOTAL USAGE for my projects I am having trouble adding both estimates and actuals together in my query can someone show me the way. I really appreciate it  SELECT    
@Select:Dim:User_def:Implied:Project:sp.id:id@,
@Select:Dim_Prop:User_Def:Implied:Project:sp.name:ProjectName@,
@Select:Dim_Prop:User_Def:Implied:Project:sp.unique_name:ProjectID@,
@Select:Dim_Prop:User_Def:Implied:Project:sp.description:Description@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.prstart:ProjectStart@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.prfinish:ProjectFinish@ ,
@Select:Dim_Prop:User_Def:Implied:Project:odf.eh_smf:SMF@,
@Select:Dim_Prop:User_Def:Implied:Project:lps3.name:ProjectPhase@,
@Select:Dim_Prop:User_Def:Implied:Project:odf.eh_prj_phase:Phaseinternalid@,
@Select:Dim_Prop:User_Def:Implied:Project:odf.eh_overall_res_bdgt:OverallResourceBudget@,
@Select:Dim_Prop:User_Def:Implied:Project:dept.shortdesc:Department@,
@Select:Dim_Prop:User_Def:Implied:Project:(cmn.last_name ||', '||cmn.first_name):ProjectManger@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.prpriority:Priority@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.status_indicator:StatusIndicator@,
@Select:Dim_Prop:User_Def:Implied:Project:pp.status_comment:StatusComment@,
@Select:Dim_Prop:User_Def:Implied:Project:odf.eh_next_go_live:GoLive@,
@Select:Dim_Prop:User_Def:Implied:Project:lps.name:Customer@,
@Select:Dim_Prop:User_Def:Implied:Project:nbi.actual_hours:ACT@,
@Select:Dim_Prop:User_Def:Implied:Project:nbi.etc_hours:ETC@,
@select:dim_prop:user_def:implied:Project:cop_calc_finish_time_fct(pp.prfinish):finish_date@,
@select:dim_prop:user_def:implied:Project:cop_calc_finish_time_fct(pp.prbasefinish):base_finish_date@,
@select:metric:user_def:implied:(cop_calc_finish_fct(pp.prBaseFinish) - cop_calc_finish_fct(pp.prFinish)):days_late@
FROM        
srm_projects sp,
odf_ca_project odf,    
prj_projects pp ,
cmn_sec_users cmn ,
departments dept,
prj_obs_associations obs,
cmn_lookups_v lps,
cmn_lookups_v lps3,
nbi_project_current_facts nbi
where
sp.id=odf.id (+)
and pp.prid=sp.id
and nbi.project_id=sp.id  
and cmn.id=pp.manager_id
and obs.record_id=sp.id
and obs.unit_id=dept.obs_unit_id
and obs.table_name='SRM_PROJECTS'
and lps.lookup_code (+)= odf.EH_CUSTOMER
and lps.lookup_Type (+)='EH_CUSTOMER'
and lps.language_code (+)= 'en'
and lps3.lookup_code (+) = odf.eh_prj_phase
and lps3.lookup_Type (+)='EH_PRJ_PHASE'
and lps3.language_code (+)= 'en'
and lps3.is_active(+)=1
and   sp.is_active=1
and odf.eh_non_prj=0
and sp.is_template=0
and sp.is_program=0
AND           @FILTER@

Outcomes