D B
asked on
SELECT with Joins returning ambiquous data
I have the following query (stripped down somewhat-it is part of a UNION)
SELECT E.Classification,
tblPayrollRegisterEmployee .Departmen tCode,
E.Lname,
E.Fname,
MAX(tblPayrollRegisterPaym ents.PayTy peCode) AS PayTypeCode,
SUM(tblPayrollRegisterPaym ents.Hours ) AS OTHours,
SUM(tblPayrollRegisterPaym ents.Amoun t) AS OTPay,
0 AS HourlyPayRate,
0 AS YTDCompHours,
0 AS YTDHCompHours
FROM tblPayrollRegisterPayments INNER JOIN
HumanResourcesTest.dbo.Emp loyee E ON tblPayrollRegisterPayments .EmpID = E.emp_no INNER JOIN
tblPayrollRegisterEmployee ON tblPayrollRegisterPayments .EmpID = tblPayrollRegisterEmployee .EmpID AND
tblPayrollRegisterPayments .PayPeriod EndingDate = tblPayrollRegisterEmployee .PayPeriod EndingDate
WHERE tblPayrollRegisterEmployee .Departmen tCode LIKE CASE @Facility
WHEN 1 THEN '494%'
WHEN 2 THEN '555%'
END
AND (tblPayrollRegisterPayment s.PayTypeC ode = 'Overtime')
AND (tblPayrollRegisterPayment s.PayPerio dEndingDat e BETWEEN @StartDate AND @EndDate)
GROUP BY E.Classification, E.Lname, E.Fname, E.emp_no, tblPayrollRegisterEmployee .Departmen tCode
tblPayrollRegisterEmployee is the 'Parent' table and tblPayrollRegisterPayments is its child.
tblPayrollRegisterEmployee contains and employee ID which I can use to get additional information from Employee (name mainly)
The problem: The Employee table I am using is a 'temporary' one I built for the purpose of this report. It contains merged data from two physical locations plus 'archived' data. The archived data is needed because the report produced from this data is grouped by classification and classification is in the employee table and if archived (terminated) records were not included, their data would not be part of the output. Rather than tackling the problem from the perspective of reloading all the employee data, I would rather work it out in the procedure.
Anyway, I digressed. The problem is that we have a number of employees who terminated employment then rehired. Also, because of dual functions, some employees are in the employee database at both locations. When that is the case, I double (or even triple) the totals. Can someone suggest an easy way (maybe changing the JOIN statements?) to insure that for each payment, I only retrieve a single employee record.
A couple of 'rules' that might be able to be used: If they were terminated and rehired, there is a HireDate in the employee table and I could possibly get the most recent using MAX(E.HireDate) in some way. If they are in both loactions' tables, there is a possibility (not guarantee) that E.Department (which I am not currently using) would be different and the 'real' employee record's e.department would be the same as tblPayrollRegisterEmployee .Departmen tCode
I am not too concerned really in getting the 'correct' employee record, since what I am mainly interested in is the e.Classification and if they terminated and were rehired, there is a 99% probability that they hired back in the same classification and there is a 99% probability that the classification between locations is the same.
SELECT E.Classification,
tblPayrollRegisterEmployee
E.Lname,
E.Fname,
MAX(tblPayrollRegisterPaym
SUM(tblPayrollRegisterPaym
SUM(tblPayrollRegisterPaym
0 AS HourlyPayRate,
0 AS YTDCompHours,
0 AS YTDHCompHours
FROM tblPayrollRegisterPayments
HumanResourcesTest.dbo.Emp
tblPayrollRegisterEmployee
tblPayrollRegisterPayments
WHERE tblPayrollRegisterEmployee
WHEN 1 THEN '494%'
WHEN 2 THEN '555%'
END
AND (tblPayrollRegisterPayment
AND (tblPayrollRegisterPayment
GROUP BY E.Classification, E.Lname, E.Fname, E.emp_no, tblPayrollRegisterEmployee
tblPayrollRegisterEmployee
tblPayrollRegisterEmployee
The problem: The Employee table I am using is a 'temporary' one I built for the purpose of this report. It contains merged data from two physical locations plus 'archived' data. The archived data is needed because the report produced from this data is grouped by classification and classification is in the employee table and if archived (terminated) records were not included, their data would not be part of the output. Rather than tackling the problem from the perspective of reloading all the employee data, I would rather work it out in the procedure.
Anyway, I digressed. The problem is that we have a number of employees who terminated employment then rehired. Also, because of dual functions, some employees are in the employee database at both locations. When that is the case, I double (or even triple) the totals. Can someone suggest an easy way (maybe changing the JOIN statements?) to insure that for each payment, I only retrieve a single employee record.
A couple of 'rules' that might be able to be used: If they were terminated and rehired, there is a HireDate in the employee table and I could possibly get the most recent using MAX(E.HireDate) in some way. If they are in both loactions' tables, there is a possibility (not guarantee) that E.Department (which I am not currently using) would be different and the 'real' employee record's e.department would be the same as tblPayrollRegisterEmployee
I am not too concerned really in getting the 'correct' employee record, since what I am mainly interested in is the e.Classification and if they terminated and were rehired, there is a 99% probability that they hired back in the same classification and there is a 99% probability that the classification between locations is the same.
You're saying HumanResourcesTest.dbo.Emp loyee is the temporary "merged" data or tblPayrollRegisterEmployee ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
HumanResourcesTest.dbo.Emp loyee is the temporary table. I could even get by with delete query that would identify records in the Employee table and delete duplicates (based on the field EmpID) while leaving one of the duplicate employee records in the table.
ASKER
jaanth: It worked. Some might say, as Drew Carey puts it on Whose Line, the points don't matter, but to he_ _ with him. You've earned them!
I had to make a slight change (used MAX() with each of the fields and pulled LName and FName out of the GROUP BY clause) because one employee was actually in there with two different first names. Figured this would be a more permanent fix than deleting that one record.
Thanks bunches.
I had to make a slight change (used MAX() with each of the fields and pulled LName and FName out of the GROUP BY clause) because one employee was actually in there with two different first names. Figured this would be a more permanent fix than deleting that one record.
Thanks bunches.