Current Salary Amount

[meinpsl]

I'm trying to produce a query that will bring up a list of all employees with their current salary amount. The table however, has all changes for an employee and reads as follows:

Table Name(NBRJOBS)
Employee ID-- Date of Change -- Salary Amt
1234 ----- 01/01/2003 -------- 500.00
1234 ----- 05/01/2003 ----- --- 600.00
1234 ----- 11/01/2003 ---- --- 700.00
2222 ----- 03/05/2004 ------- 200.00
2222 ----- 12/05/2004 ---- --- 300.00
2222 ----- 02/01/2005 ---- --- 300.00

It will be necessary to create a report to list out all employees and their current salaries. Any suggestions?

 

[pbaldy]

One way would be 2 queries. The first groups on employee and finds the max date of change for each. The second query includes the first query and the table, joined on date and employee, and returns the salary.

 

[meinpsl]

Max date

Paul
Thanks for responding. The problem I'm having is with the max date. It brings up the most current date in the file, but the most current date can be different for each employee, so I only get one employee when I do the max date. It brings up the one employee with the most current date. I need each employee's most current date.
Thanks again

 

[pbaldy]

The first groups on employee and finds the max date of change for each.

You misunderstood. The query would look something like this:

SELECT EmployeeID, Max(ChangeDate) AS MaxDate
FROM TableName
GROUP BY EmployeeID

 

[meinpsl]

Max Salary

Thanks for the help. I appreciate it. That gets me the max salary date. When I add the salary field in though, it brings up multiple dates again and multiple salaries;
Very confusing to me.

SELECT Employee ID, Max(EFFECTIVE_DATE) AS MaxDate, SALARY
FROM TABLE
GROUP BY Employee ID;

 

[meinpsl]

max salary

Thanks Pbaldy. I worked it out. I really appreciate your help.