4

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;
Improve this question
1
  • It's SQL Server 2012 Commented Aug 28, 2014 at 15:29

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

@linger That doesn't appear to work. I tried a DISTINCT statement as well and they both return the same duplicated rows. Using the SELECT TOP(1) statement only returns a single row, where I need to see 112 past pay periods so I adjusted for that. I'm just puzzled why the data is duplicated.
@lovetofly38, check out my edit, dow that work better for you?
@linger thank you for the edited recommendation. However it returns the same duplicates. I used your edits exactly as listed. Could it be related to the fact I'm querying the data using "views" instead of the raw data table? Our IT group developed views for all users who have access to the corporate data warehouse. If I query the data FROM PayrollFile only, I get what I would expect which is 112 pay periods, each one tied to a specific division, with the correct uniform allowance associated with it. I need to JOIN to the PersonnelFile though because it contains the JOBCODE and HIREDATE.
@linger the JOBCODE and HIREDATE drive the eligibility for uniform allowance so that's why I need them.
@linger I think I'm just going to use multiple queries for this. Thanks for your help. I'm going to query the personnel file first to get a listing of all employees who have worked in the specific job series during a date range, then take that listing of SSNs and use it in another query to show me each pay period for those SSN's referenced. A little more work than I want to do but effective nevertheless. Thanks again for your efforts!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.