CODEWITHSUNDEEP
sqlsql-servert-sql
Joseph
Joseph

Reputation: 51

SQL Query-Duplicate Data

Our company currently has 150+ divisions nationwide. For certain positions, we pay for our employees uniforms by reimbursing them each pay period. Each division has its own personnel and payroll office, but all paychecks are processed centrally by a third party. There's a small risk that employees are receiving duplicate uniform reimbursements each pay period due to transfers within the company. For example, Employee A transfers to a new division and continues getting uniform reimbursement from the old division as well as the new one. This shouldn't be happening given our checks are processed centrally but I want to be able to confirm it's not by pulling the data from our corporate data warehouse.

The query I wrote to test this appears to be unnecessarily duplicating the PAYDATE and uniform allowance (UACPPD) based on the number of different divisions the employee has worked in. For this employee, he has worked in five different divisions, and each time he transfers the local HR office inputs a new hire date into their local system, thus he has five different rows for the same PAYDATE. I'm sure this has to do with the repeating values, I'm just not sure how to structure my query to prevent it. I have to pull from both tables, so that's not an option and it appears that the duplication exists regardless the type of join i use. Thanks in advance for any guidance!

SELECT
P1.store_number as ST#
,P1.store_transferred_from as XST#
,P1.employee_name as NAME
,P1.ssn as SSN
,P2.pay_date as PAYDATE
,P2.uniform_allowance_amt_cppd AS UACPPD
,P1.job_series as JOBCODE
,P1.hire_date as HIREDATE


FROM PersonnelFile as P1
LEFT JOIN PayrollFile as P2
ON P1.SSN = p2.SSN
WHERE P2.pay_date > '2010-05-14'
and P2.uniform_allowance_amt_cppd in (8.25,8.50,300)
and P1.jobs_series in ('2380','1458')
and P1.ssn = '123456789'

ORDER BY P1.ssn,P2.pay_date;

Upvotes: 4

Views: 106

Answers (1)

Linger
Linger

Reputation: 15068

It sounds like you want the most current record. So why not order by P1.hire_date DESC and then limit the results to 1:

SELECT TOP(1) P1.store_number as ST#,
       P1.store_transferred_from as XST#,
       P1.employee_name as NAME,
       P1.ssn as SSN,
       P2.pay_date as PAYDATE,
       P2.uniform_allowance_amt_cppd AS UACPPD, 
       P1.job_series as JOBCODE,
       P1.hire_date as HIREDATE
FROM PersonnelFile as P1
LEFT JOIN PayrollFile as P2 ON P1.SSN = p2.SSN
WHERE P2.pay_date > '2010-05-14'
  AND P2.uniform_allowance_amt_cppd in (8.25,8.50,300)
  AND P1.jobs_series in ('2380','1458')
  AND P1.ssn = '123456789'
ORDER BY P1.hire_date DESC, P1.ssn, P2.pay_date

EDIT:

SELECT P1.store_number as ST#,
       P1.store_transferred_from as XST#,
       P1.employee_name as NAME,
       P1.ssn as SSN,
       P2.pay_date as PAYDATE,
       P2.uniform_allowance_amt_cppd AS UACPPD, 
       P1.job_series as JOBCODE,
       P1.hire_date as HIREDATE
FROM 
(
  SELECT TOP(1) * FROM PersonnelFile as PS1
  WHERE PS1.jobs_series in ('2380','1458')
    AND PS1.ssn = '123456789'
  ORDER BY PS1.hire_date DESC
) AS P1
LEFT JOIN PayrollFile as P2 ON P1.SSN = p2.SSN
WHERE P2.pay_date > '2010-05-14'
  AND P2.uniform_allowance_amt_cppd in (8.25,8.50,300)
ORDER BY P1.ssn, P2.pay_date

You are getting duplicates because of the way that PersonnelFile has to be joined to PayrollFile via P1.SSN = P2.SSN. You are adding a new record each time the employees transfers with the same SSN. So each record in PersonnelFile will link to every record in the PayrollFile with the same SSN.

Upvotes: 2

Related Questions