Geek Answers Handbook

SQL-Query-Duplicate Data

At present, our company has more than 150 divisions throughout the country. At certain positions, we pay for the uniform of our employees, reimbursing them every payment period. Each unit has its own personnel and salary office, but all salaries are processed centrally by a third party. There is a small risk that employees receive the same lump-sum payments for each payment period in connection with transfers within the company. For example, Employee A moves into a new unit and continues to receive uniform compensation from the old unit as well as the new one. This should not happen, because our checks are processed centrally, but I want to confirm this without pulling data from our corporate data warehouse.

In the query that I wrote to verify this, duplication of PAYDATE and uniform permission (UACPPD) on the basis of the number of different departments the employee worked in seems redundant. For this employee, he worked in five different departments, and each time that he transfers, the local HR department enters a new hiring date into his local system, so he has five different lines for the same PAYDATE. I'm sure this is due to duplicate values, I'm just not sure how to structure my query to prevent it. I have to extract from both tables, so that is not an option, and it seems that duplication exists regardless of the type of connection that I use. Thank you 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;
+4
1

, . , P1.hire_date DESC, 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

- , PersonnelFile PayrollFile P1.SSN = P2.SSN. , SSN. PersonnelFile PayrollFile SSN.

+2

More articles:


All Articles