Get last record only if multiple exist
-
I have a table that may or may not contain 2 records for one employee. 90% of the time employees only have 1 record, however sometimes, two can exist. How can retrieve just the last record in a table based on a date if two records exist for the same employee on the same date and time?
-
Select * from dbo.Employees E1 WHERE not EXISTS (SELECT * FROM dbo.Employees E2 WHERE E2.HireDate > E1.HireDate AND E1.LName = E2.LName AND E1.FName =E2.Fname.... /* keep going untill you have enough filter to flush out all doubles */)
-
Jim
You haven't given us much to go on! Assuming you're not bothered which record you retrieve, something like this:
SELECT TOP 1 EmpName, EmpDateTime, EmpData
FROM Employees
WHERE EmpID = @EmpID
John
-
I always want the last record in the table for the date I specify, it will have the most current data.
Thanks for the help everyone!!!!!!!!!!!!!!
-
Jim
Yes - you did specify "based on a date". So you can do it like this:
SELECT TOP 1 EmpName, EmpDateTime, EmpData
FROM Employees
WHERE EmpID = @EmpID
ORDER BY EmpDateTime DESC
John
-
Good point. My Versions was assuming you needed all employees at the same time.
-
I do all need all the employees at one time, just thier most recent record in the table.
select top 1 *
from phbasic
where pay_period_end = '11/25/2006'
only returned one record (total) as I expected, I need it to return the most current record for each employee.
-
Can you post some sample data so that I can tune my query. I think it's the only one so far that can solve this problem.
-
So it's not just a simple.....
SELECT EmpName, MAX(EmpDateTime)
FROM Employees
GROUP BY EmpName...
**ASCII stupid question, get a stupid ANSI !!!** -
2 employees could theorically have the same name, even the same adress/phone numbers. The only unique field should be empID (which even here where I work is not unique but that's another story).
I would strongly suggest cleaning the data or seing if we couldn't alter the db design here.
-
Sorry - didn't realize that this was a "cleaning operation"....
**ASCII stupid question, get a stupid ANSI !!!** -
Maybe it's not. But it looks like it at this point. But even with clean data you could have 2 employees with almost identical profiles except for SSN and bank accounts of course. But then again you may not have that data handy to make distinctions :-).
-
The only thing different between the two records would be the check number and amount. Everything is exactly the same, I can't even use date/time because they are the same.
select * from phbasic
where 1 < (select count(*)
from phbasic t2
where t2.pay_period_end = '11/25/2006'
and t2.emp_id = phbasic.emp_id
and t2.company = phbasic.company
and t2.pay_period_end = phbasic.pay_period_end)
will bring up every one with two checks for this pay period, I want just the largest check number of the two.
-
You may be making overly complicated. Can you please post your DDL for the table and explain the relation is.
Otherwise let me make an example here and see if this is kind of what you are looking for. Suppose I have an Employee Pay table which contains a new record each time the pay changes for historical purposes
Employee_Wages
Employee_ID,
Pay_Rate,
Effective_Date
... --Additional stuff not relavent to the example.
I want based on my a date or even today to give me the Pay_Rate in effect at the time.
DECLARE @Date datetime
SET @Date = GETDATE()
SELECT
Employee_ID,
(
SELECT
Pay_Rate
FROM
dbo.Employee_Wages iQ1
WHERE
iQ1.Employee_ID = EW.Employee_ID AND
iQ1.Effective_Date = (SELECT max(iQ2.Effective_Date) Effective_Date FROM dbo.Employee_Wages iQ2 WHERE iQ1.Employee_ID = iQ2.Employee_ID AND iQ2.Effective_Date <= @Date))
FROM
dbo.Employee_Wages EW
What you want will be very similar to this. And hopefully you cannot have duplicate dates for the same employee_id. And if you need to you can add an Employee_ID variable for a specific Employee_ID.
-
select * from phbasic Main
INNER JOIN
(select EmpID, MAX(CheckNumber) AS CheckNumber
from dbo.phbasic
where t2.pay_period_end = '11/25/2006'
group by EMPID) dtMaxCheck
ON Main.CheckID = dtMaxCheck.CheckID
--optional but I doubt it is required
--AND Main.EmpID = dtMaxCheck.EmpID
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply