Issue with SQL Query from two tables that have a 1 to many relationship

pts.
Tags:
SQL
Thanks all for your help to my question last week. I did manage to get two of the totals I needed for the query. I am still having problems getting the second total and realize now the tables I am pulling the data from have a 1 to many relationship. I am trying to print a report of time clock data by totaling the OT hours and number of unauthorized records for a given store within a date range. The detail table contains records for every card punch so there can be more than one record with a time code for a employee for a given day. The summary record contains the unauthorized record flag I need and contains only one record for the employee for the day. My first query had the OT hours correct but inflated the counts becauce it was counting each detail record for the the one summary. I added a case statement and the numbers are better but still wrong. When I used the subquery (select count(*) from view_work_summary where wrks.unauthorized = 'N') all the counts are the same for every store. How can I get the count? Do I need a subquery? Any ideas on how to structure it? Here is the query: select workbrain_team.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, sum(case when view_work_summary.wrks_authorized = 'N' then 1 else 0 end) as totua from view_work_detail, view_work_summary, employee_team, workbrain_team , sec_wb_team_tree where view_work_summary.wrks_id = view_work_detail.wrks_id and 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 Here's what I am getting: WBT_NAME TOT_OT1 TOT_OT2 TOTUA ---------------------------------------- ---------- ---------- ---------- 0220 1.5 0 0 0226 .1 0 0 0231 1.08333333 0 2 0248 .5 0 0 0271 0 0 0 0288 0 0 1 0403 .366666667 0 125 0528 4.01666667 0 20 0529 0 0 2 0697 0 0 3 Thanks in advance for your help!

Answer Wiki

Thanks. We'll let you know when a new response is added.

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:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following