SQL Query -History Table

[psamedy]

Hello all
I have a SQL table in my database that records salary changes of employees historically . So a particular person may have had a salary change any number of times within a given time period. What I’d like to do is write a query retrieve the last three pay changes for a particular person. I’m new to T-SQL but I was thinking along the lines of using a cursor to store the records, looping through each row and with a case statement printing the most recent pay changes by using datdiff(max(pay_start_date), pay_start_date).

There must be an easier way? Please help

The fields that I need to use are:

person_id
pay_start_date
pay_end_date
pay_amount

Thanks for your help

Pat

 

[Golom]

Try
[blue][tt]
Select T.Person_id,
T.Pay_Start_Date,
T.Pay_End_Date,
T.Pay_Amount

From tbl T

WHERE T.Pay_Start_Date IN

(Select TOP 3 Pay_Start_Date From tbl X
Where X.Person_ID = T.Person_ID
Order By Pay_Start_Date DESC )
[/tt][/blue]

 

[Pattycake245]

Pat,

How about this:

select top 3 person_id, pay_start_date, pay_end_date, pay_amount
from SQLtable
where person_id = 1111
order by pay_end_date desc

Tim

 

[psamedy]

Thanks guys this TOP function was exactly what i needed. A question though, what reason is there for this in the subquery.

Order By Pay_Start_Date DESC

is it necessary?

 

[Pattycake245]

Yes it is necessary because it gives you the top three records starting from the latest date descending to the earliest. If you do not include desc you'll get the earliest three records as ascending is the default.

Tim

 

[Golom]

A slight clarification ...

This could return more than three records if there are multiple records having the same value for the third latest date. If you had a table with
[tt]
02/25/2004
02/24/2004
02/23/2004
02/23/2004
[/tt]
Then all four records would be returned.

 

[Pattycake245]

Good point Golom. Then:

select distinct top 3 person_id, pay_start_date, pay_end_date, pay_amount
from SQLtable
where person_id = 1111
order by pay_end_date desc