Hi Trina,
Assuming you are using at least Oracle 8 – you could use an inline view to pre calculate the summary level data and then join this to the detail records. I have tried to rewrite your query so:
SELECT my_view_summary.wbt_name,
sum(decode(view_work_detail.htype_name,’OT1′,view_work_detail.wrkd_minutes/60,0)) tot_OT1,
sum(decode(view_work_detail.htype_name,’OT2′,view_work_detail.wrkd_minutes/60,0)) tot_OT2,
my_view_summary.totua
FROM
(SELECT workbrain_team.wbt_name,
view_work_summary.wrks_id,
sum(case when view_work_summary.wrks_authorized = ‘N’ then 1 else 0 end) as totua
FROM
view_work_summary,
employee_team,
workbrain_team ,
sec_wb_team_tree
WHERE
view_work_summary.emp_id = employee_team.emp_id
and employee_team.wbt_id = workbrain_team.wbt_id
and workbrain_team.wbt_id = sec_wb_team_tree.wbt_id
and view_work_summary.wrks_work_date >= (TO_DATE(SUBSTR(‘#dpStartDate.value#’,1,8),’yyyymmdd’) )
and view_work_summary.wrks_work_date < (TO_DATE(SUBSTR(‘#dpEndDate.value#’,1,8),’yyyymmdd’) + (1))
and sec_wb_team_tree.wbt_parent_id = #dbTeam.valueId#
group by workbrain_team.wbt_name,
view_work_summary.wrks_id) my_view_summary
, view_work_detail
where
my_view_summary.wrks_id = view_work_detail.wrks_id
GROUP BY my_view_summary.wbt_name
Note that the calculation of totua is done inside the inline view(a virtual table) and you then join this to the view_work_detail records.
Also did you know that you can easily strip the time portion from a date filed by using the TRUNC function? I see you are using substr – just do TRUNC(dpStartDate.value) and this will strip off the time.
Hope some of this helps
regards
Simon.
Discuss This Question: