Logic Problem (SQL Query)

[alwayshouston]

Hi All
I have a table that records employee salary. First time when someone loads the data, the Original field is marked "Y" and Modified field is marked "N". If that particular employee record is updated, the original field is update "N" and Modified field is updated with "Y". Below is the table structure with records in which employee ID 100 is modified:

EmpID Salary Original Modified
100 1000 Y N
200 2000 Y N
300 3000 Y N
100 1500 N Y


I need to write a query that would give me the most updated/modified record for each employee as below:

EmpID Salary
100 1500
200 2000
300 3000

Can someone please help me write a query that would generate above output? Thanks in advance!

 

[psemianonymous]

Instead of storing "original" and "modified" flags, you can simply put a table with:

EmployeeID
Salary
DateOfEntry


Then do:

SELECT * FROM tblSalary WHERE
DateOfEntry IN (SELECT Max([DateOfEntry]) FROM tblSalary GROUP BY EmployeeID)


You have to watch out if two entries are made on the same day. Set all three fields as the composite primary key, and this problem is eliminated.



If you absolutely MUST use your current setup, then use the Last() aggregate function, which is about the only way to achieve what you need.

 

[Golom]

This is a bit tricky but ...
[tt]
SELECT S1.EmpID, MIN(S1.Salary) As [CurrentSalary]
FROM Salaries As S1
GROUP BY S1.EmpID
HAVING Count(*) = 1

UNION ALL

SELECT S1.EmpID, MAX(S1.Salary)
FROM Salaries As S1
WHERE S1.Original = FALSE
GROUP BY S1.EmpID
HAVING Count(*) >= 1

ORDER BY 1
[/tt]
The MAX in the second Select just takes into account the possibility that there may be more than one record where the salary has been modified.

 

[psemianonymous]

Eep, just noticed a 'bug' in my post:


[tt]You have to watch out if two entries are made on the same day. Set all three the EmployeeID and DateOfEntry fields as the composite primary key, and this problem is eliminated.[/tt]

 

[alwayshouston]

Thanks Golom! One word comes to mind "GENIUS"! after reading your reply. Your solution works perfectly. I have a performance question in this regard. Would above solution create a problem if I have 100,000 plus records in the table and instead of one field (salary) I have 15 more fields? If it creates performance issue, do you know any other way of getting the output.

Again THANK YOU very much!

 

[Golom]

If you have 100,000+ records then you should ensure that you have indexes built on the EmpID and Salary fields. If you don't then there's going to be a sequential search (i.e. long, long time) to resolve the Group By and aggregate functions.

If there are 15 (or whatever other fields) that shouldn't create a performance issue UNLESS you are introducing additional selection criteria that involve those fields. The simple fact that other fields exist but are not part of the query should not have a performance impact.

BTW: The solution that I provided assumes that salaries, when they are modified, always increase (i.e. no salary cuts) If that's not the case then the SQL won't give the right answers.

You should really look at foolio12's suggestions about incorporating date stamps. If you have those then all this complex coding becomes unnecessary.

 

[alwayshouston]

Thanks!
I can incorporate the time stamp to my table, but I still need a functionality that would tell me the modified record regardless of time. For instance, I am getting one salary file from HR and I mark this file records Original ='Y'. I get another file from the ADP or Owner that edits the record and I mark Modified ='Y'. I need to give the preference to the ADP or OWNER regardless of whether I recieve the file from owner prior to HR or after HR. I hope this makes sense. Your solution is not dependent on whether salary increases or decreases. It gives the right answer regardless of increase or decrease in salary for modified record.

 

[Golom]

What I meant was ... suppose you had
[tt]
EmpID Salary Original Modified
100 1000 Y N
100 2000 N Y
100 1500 N Y
[/tt]
The SQL would return "2000" and not "1500". If you have one and only one "Modified" record then there is no issue.

As to "... tell me the modified record ..." Presumably the first (i.e. earliest time stamp) is the original and any later ones are modifications.

 

[alwayshouston]

Well, you are right on the condition that there has to be ONLY ONE modified record in order for query to work. I have created a composite primary key to take care of that. As for your other concern, there is not known sequence that tells whether modified records are inserted first or the original records.

 

[alwayshouston]

I forgot to thank Mr. Foolio for his insight and feedback. Thanks ALL )