February 21, 2006 at 2:44 pm
Hi all,
I am trying to get one record per employee for the most current pay period this employee was paid on.
One employee's last checkdate is on 2/14/06, while another employee's last checkdate can be 1/31/06.
UPR00100 is employee master table, one record per employee.
UPR00200 is employee address table, one employee can have more than 1 address.
UPR30300 is employee paycheck transaction table, one employee has all history paycheck records.
The T-SQL I wrote displays multiple records for the employee because the multiple checkdates, can someone help me on getting one record per employee for the lastest chekdate?
SELECT u1.EMPLOYID, u1.LASTNAME, u1.FRSTNAME, u1.SOCSCNUM, u1.STRTDATE,u2.ADDRESS1, MAX(u3.CHEKDATE)
FROM UPR00100 u1 INNER JOIN
UPR00102 u2 ON u1.EMPLOYID = u2.EMPLOYID INNER JOIN
UPR30300 u3 ON u1.EMPLOYID = u3.EMPLOYID AND u2.EMPLOYID = u3.EMPLOYID AND u1.jobtitle = u3.jobtitle
WHERE year1 = 2006
GROUP BY u1.EMPLOYID, u1.LASTNAME, u1.FRSTNAME, u1.SOCSCNUM,u1.STRTDATE, u2.ADDRESS1,u3.CHEKDATE
Thank you.
February 21, 2006 at 2:49 pm
Take u3.CHECKDATE out of your GROUP BY
I wasn't born stupid - I had to study.
February 21, 2006 at 3:00 pm
>>UPR00200 is employee address table, one employee can have more than 1 address.
You haven't provided enough requirements to enable a solution.
If an employee has more than 1 address, and you want address in your resultset, which address do you want to see ?
February 21, 2006 at 3:08 pm
That's true too. If that table has a "most recent date", (or something like that) you can do it as an INNER JOIN( SELECT Address, MAX( RecentDate) FROM EmployeeAddress GROUP BY Address) AS Address, then use that in your original select.
I wasn't born stupid - I had to study.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply