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