Query question...
-
I have a view which returns HR information. My problem is that I could have multiple rows for the same person because the system we use adds a new row of data every time the status of the employee changes, i.e. promotion, title, etc.
As part of the column data I get the last update date which tells me which row is the one that I should keep. I got a query that will return me what I need for employees that have multiple records, but does not return the ones that only contain one entry in the database.
I can not come up with a query that will return me all the rows with the latest update date, and eliminate all others.
Any ideas/suggestions would be greatly appreciated...
-
Perhaps something like this:
SELECT p.EmployeeID, p.LastName, p.FirstName
FROM Personnel p JOIN
(SELECT EmployeeID, MAX(UpdDate) LastDate
FROM Personnel
GROUP BY EmployeeID) x ON p.EmployeeID = x.EmployeeID AND p.UpdDate = x.LastDate
--Jonathan -
Or something like this :- Which does away with the extra join and the group by
SELECT p.EmployeeID, p.LastName, p.FirstName, p.UpdDate
FROM Personnel p
where p.UpdDate=(Select max(Upddate) from Personnel x where p.EmployeeID = x.EmployeeID)
-
Both solutions work. Thank you for your help.
-
It sounds to me like you have data redundancy in your database design. If possible it might be better to look at further normalising your design to eliminate the problem, rather than having to write clever queries.
Just a thought...
"See how deep the rabbit hole goes..." -
It sounds to me like you have data redundacny in your database design. If possible it might be better to look at further normalising your design to eliminate the problem, rahter than having to write clever queries.
Just a thought...
"See how deep the rabbit hole goes..." -
I don't think this is implied. It is easily conceivable to need such a query in a properly normalized database. This is a history table of each change in an employee's status, and the OP wishes to get the information about the latest change for each employee, i.e.:
CREATE TABLE EmployeeStatusHistory(
EmployeeID int REFERENCES Personnel,
UpdDate datetime NOT NULL DEFAULT GETDATE(),
TitleID tinyint REFERENCES Titles,
Salary money NOT NULL,
ChangedBy int REFERENCES Personnel,
ChangeType tinyint REFERENCES HRStatusChangeTypes,
PRIMARY KEY(EmployeeID,UpdDate))
The Personnel table may have the current Title and Salary information, but we want to know when the last change was made, what type of change it was, and who made the change...
An example using Northwind would be: "show me last order from each customer."
--Jonathan -
Ah, humble apologies
I guess it helps to ask for more info before jumping to design conclusions. What you say makes absolute sense. We also have much the same sort fo scenario wiht one of our big db's which stores such items as stock cost history in the same fashion.
"See how deep the rabbit hole goes..."
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply