详细的加班报告-工时增加了一倍

Question

我正在为董事们编制一份详细的加班报告，到最后，加班时间增加了一倍，但并非所有员工都是如此。在我的查询中，我使用的是Employee 802，加班时间为95，但显示为90。我已经尝试了我所知道的一切。感谢我能得到的任何帮助！！

USE munprod
select 
pr_employee_master.a_location_primary [Dept Code], pr_employee_master.a_location_p_desc as Dept, 
pr_employee_master.a_employee_number [Emp Number],
CONCAT(pr_employee_master.a_name_last, ', ',pr_employee_master.a_name_first)[Employee Name],
pr_employee_master.a_job_class_desc [Job Desc], 
sum(pr_earn_history.eh_hours_worked) [OT Hours],
sum(pr_earn_history.eh_total_amount) [Total OT Amount],
max(pr_base_pay.a_salary_annual) [Annual_Salary]
from pr_employee_master, pr_earn_history, pr_base_pay
where pr_employee_master.a_employee_number = pr_earn_history.a_employee_number 
and pr_employee_master.a_employee_number = pr_base_pay.a_employee_number
and pr_earn_history.a_pay_type in ('200','201') and eh_end_date between '2018-04-01' and '2018-06-30'
and pr_employee_master.a_employee_number = '802'
group by  
pr_employee_master.a_location_primary, pr_employee_master.a_location_p_desc, 
pr_employee_master.a_employee_number, pr_employee_master.a_name_last, 
pr_employee_master.a_name_first, pr_employee_master.a_location_p_short, 
pr_employee_master.a_job_class_desc

Answer 1

当您使用CROSS JOIN格式(FROM子句中的逗号(时，您需要在WHERE子句中更加明确

试试这个，让我知道它是否有效。更好的解决方案传入

FROM pr_employee_master, pr_earn_history, pr_base_pay
WHERE 
pr_employee_master.a_employee_number = pr_earn_history.a_employee_number 
AND     pr_employee_master.a_employee_number = pr_base_pay.a_employee_number
AND     pr_earn_history.a_employee_number = pr_base_pay.a_employee_number -- Added*.
AND     pr_earn_history.a_pay_type in ('200','201') and eh_end_date between '2018-04-01' AND '2018-06-30'
AND     pr_employee_master.a_employee_number = '802'

WHERE子句的更好格式与上面所写的相同。

FROM pr_employee_master as master  
INNER JOIN pr_earn_history  as histroy 
ON master.a_employee_number = histroy.a_employee_number
INNER JOIN pr_base_pay as base 
ON  base.a_employee_number = history.a_employee_number
WHERE 
pr_earn_history.a_pay_type in ('200','201') and eh_end_date between '2018-04-01' and '2018-06-30'
AND pr_employee_master.a_employee_number = '802'