Solved

SELECT with Joins returning ambiquous data

Posted on 2006-06-28
4
206 Views
Last Modified: 2010-08-05
I have the following query (stripped down somewhat-it is part of a UNION)

      SELECT      E.Classification,
                  tblPayrollRegisterEmployee.DepartmentCode,
                  E.Lname,
                  E.Fname,
                  MAX(tblPayrollRegisterPayments.PayTypeCode) AS PayTypeCode,
                  SUM(tblPayrollRegisterPayments.Hours) AS OTHours,
                  SUM(tblPayrollRegisterPayments.Amount) AS OTPay,
                  0 AS HourlyPayRate,
                  0  AS YTDCompHours,
                  0  AS YTDHCompHours
      FROM            tblPayrollRegisterPayments INNER JOIN
                  HumanResourcesTest.dbo.Employee E ON tblPayrollRegisterPayments.EmpID = E.emp_no INNER JOIN
                  tblPayrollRegisterEmployee ON tblPayrollRegisterPayments.EmpID = tblPayrollRegisterEmployee.EmpID AND
                  tblPayrollRegisterPayments.PayPeriodEndingDate = tblPayrollRegisterEmployee.PayPeriodEndingDate

      WHERE      tblPayrollRegisterEmployee.DepartmentCode LIKE CASE @Facility
                              WHEN 1 THEN '494%'
                              WHEN 2 THEN '555%'
                              END
      AND            (tblPayrollRegisterPayments.PayTypeCode = 'Overtime')
      AND            (tblPayrollRegisterPayments.PayPeriodEndingDate BETWEEN @StartDate AND @EndDate)
      GROUP BY       E.Classification, E.Lname, E.Fname, E.emp_no, tblPayrollRegisterEmployee.DepartmentCode

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.DepartmentCode

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.
0
Comment
Question by:dbbishop
  • 2
4 Comments
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 17003609
You're saying  HumanResourcesTest.dbo.Employee is the temporary "merged" data or tblPayrollRegisterEmployee?
0
 
LVL 7

Accepted Solution

by:
jaanth earned 500 total points
ID: 17003708
dbbishop:

So if I am reading your description correct, the HumanResourcesTest.dbo.Employee  table has the duplicate records in it?

If so, have you thought about doing subquery on it instead of joining to the table directly?

So,
HumanResourcesTest.dbo.Employee E

would be something like

(Select Emp_No, Lname, FName, min(Classification) as Classification from HumanResourcesTest.dbo.Employee
 group by  Emp_No, Lname, FName ) E

Hope this helps,

jaanth
0
 
LVL 15

Author Comment

by:dbbishop
ID: 17003721
HumanResourcesTest.dbo.Employee 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.
0
 
LVL 15

Author Comment

by:dbbishop
ID: 17004003
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.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now