Trying to calculate from most recent of duplicate records in MSSQL

[Sector 7G]

I'm working with a SQL query for a Human Resources database. Its intended purpose is to find all the paycheck records with a check date (prckhist.chkda te ) more recent than eleven days past the employee's last raise (hrpersnl.p_pay date). It then totals the hours the employee worked from those records (prdehist.units ), and if the total is within 40 hours of a threshold (hrpersnl.p_jmi sc3) then the employee's name and information is returned, and the employee is eligible for another raise.
The problem is this: the database has been corrupted, and there are a few duplicate records in the hrpersnl table. Because of this, in some cases the query will use an incorrect last raise date (hrpersnl.p_pay date) and find the wrong number of paycheck records, throwing the total off.
My question is, how do I make the query calculate from the most recent hrpersnl record, in the event that there are duplicates? I've tried changing the 6th line in the WHERE section to the following:

AND (MAX(hrpersnl.p_paydate)+11) <= prckhist.chkdate 

I've also tried

AND (SELECT MAX(hrpersnl.p_paydate)+11) <= prckhist.chkdate 

and

AND ((SELECT MAX(hrpersnl.p_paydate)FROM hrpersnl)+11) <= prckhist.chkdate 

and a few other variants. I feel like I'm missing something really basic. Anyone have any ideas?
Thanks in advance. Full code is as follows:

 SELECT        hrpersnl.p_empno AS Employee_Number, 
        hrpersnl.p_fname AS First_Name, 
        hrpersnl.p_lname AS Last_Name, 
        hrpersnl.p_paydate AS Effective_Date,
        SUM(prdehist.units) +hrtkpers.u_hrswrk AS Total, 
        hrpersnl.p_jmisc3 AS Threshold
FROM    prdehist prdehist, hrpersnl hrpersnl, prckhist prckhist,hrtkpers
WHERE        prdehist.empno = hrpersnl.p_empno 
        AND prdehist.empno = prckhist.empno 
        AND prdehist.chknumber = prckhist.chknumber 
        AND prdehist.empno=hrtkpers.u_empno 
        AND hrpersnl.p_active <>"T" 
        AND hrpersnl.p_paydate+11 <= prckhist.chkdate 
        AND (prdehist.code = '0001')
GROUP BY    hrpersnl.p_empno
HAVING    (SUM(prdehist.units)+hrtkpers.u_hrswrk>= (hrpersnl.p_jmisc3 - 40)) AND (hrpersnl.p_jmisc3 >0) 
ORDER BY     hrpersnl.p_empno 

[iburyak]

Try this:

[PHP]SELECT hrpersnl.p_empn o AS Employee_Number ,
hrpersnl.p_fnam e AS First_Name,
hrpersnl.p_lnam e AS Last_Name,
hrpersnl.p_payd ate AS Effective_Date,
SUM(prdehist.un its) +hrtkpers.u_hrs wrk AS Total,
hrpersnl.p_jmis c3 AS Threshold
FROM prdehist prdehist, (select distinct * from hrpersnl) hrpersnl, prckhist prckhist,hrtkpe rs
WHERE prdehist.empno = hrpersnl.p_empn o
AND prdehist.empno = prckhist.empno
AND prdehist.chknum ber = prckhist.chknum ber
AND prdehist.empno= hrtkpers.u_empn o
AND hrpersnl.p_acti ve <>"T"
AND hrpersnl.p_payd ate+11 <= prckhist.chkdat e
AND (prdehist.code = '0001')
GROUP BY hrpersnl.p_empn o
HAVING (SUM(prdehist.u nits)+hrtkpers. u_hrswrk>= (hrpersnl.p_jmi sc3 - 40)) AND (hrpersnl.p_jmi sc3 >0)
ORDER BY hrpersnl.p_empn o [/PHP]

[iburyak]

[PHP]SELECT hrpersnl.p_empn o AS Employee_Number ,
hrpersnl.p_fnam e AS First_Name,
hrpersnl.p_lnam e AS Last_Name,
hrpersnl.p_payd ate AS Effective_Date,
SUM(prdehist.un its) +hrtkpers.u_hrs wrk AS Total,
hrpersnl.p_jmis c3 AS Threshold
FROM prdehist prdehist,
(select distinct * from hrpersnl) hrpersnl,
prckhist prckhist,
hrtkpers
WHERE prdehist.empno = hrpersnl.p_empn o
AND prdehist.empno = prckhist.empno
AND prdehist.chknum ber = prckhist.chknum ber
AND prdehist.empno= hrtkpers.u_empn o
AND hrpersnl.p_acti ve <>"T"
AND hrpersnl.p_payd ate+11 <= prckhist.chkdat e
AND (prdehist.code = '0001')
GROUP BY hrpersnl.p_empn o
HAVING (SUM(prdehist.u nits)+hrtkpers. u_hrswrk>= (hrpersnl.p_jmi sc3 - 40)) AND (hrpersnl.p_jmi sc3 >0)
ORDER BY hrpersnl.p_empn o [/PHP]



For some reason it didn't make my change bold but you will figure.

[Sector 7G]

Thanks for your reply.
Unfortunately that returned the following error:

3700: [Microsoft][ODBC Visual FoxPro Driver]Command contains unrecognizable phrase/keyword.

[iburyak]

Sorry, I wasn't aware it is a Fox Pro.

But still you can create a view to select distinct records from this table and use a view in this query instead of a table name.

Good Luck.