using the max(date) for a nonselected column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

using the max(date) for a nonselected column

Author

Topic

ral_ani
Starting Member

2 Posts

Posted - 2008-05-05 : 23:18:26

Hi i have a long query which goes something like this:


SELECT e.LoginID, d.Name, e.Title As 'Worktitle', e.MaritalStatus,
	e.Gender, c.FirstName + ' ' + c.LastName AS 'FullName', c.EmailAddress,
	c.Phone, a.AddressLine1, a.AddressLine2, s.Name As 'ShiftName', p.Rate,
	p.PayFrequency, t.Name
FROM EmployeePayHistory p INNER JOIN
	Employee e ON p.EmployeeID = e.EmployeeID
	INNER JOIN Contact c ON e.ContactID = c.ContactID
	INNER JOIN EmployeeDepartmentHistory ed ON ed.EmployeeID = e.EmployeeID
	INNER JOIN Shift s ON s.ShiftID = ed.ShiftID
	INNER JOIN Department d ON d.DepartmentID = ed.DepartmentID
	INNER JOIN EmployeeAddress ea ON ea.EmployeeID = e.EmployeeID	
	INNER JOIN Address a ON a.AddressID = ea.AddressID
	INNER JOIN StateProvince sp ON sp.StateProvinceID = a.StateProvinceID
	INNER JOIN SalesTerritory t ON sp.TerritoryID = t.TerritoryID
WHERE ed.EndDate IS NULL
ORDER BY e.EmployeeID

My problem is that in EmployeePayHistory there are rows repeated for the same employees, so for example, employee with ID of 2 has 3 rows and each row has different information and there's a RateChangeDate column so I want to select only the most recent RateChangeDate. But I don't want this column to appear, how do i do this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-06 : 00:07:49

You can do it with a derived table. Something like this:


SELECT e.LoginID, d.Name, e.Title As 'Worktitle', e.MaritalStatus,
	e.Gender, c.FirstName + ' ' + c.LastName AS 'FullName', c.EmailAddress,
	c.Phone, a.AddressLine1, a.AddressLine2, s.Name As 'ShiftName', p.Rate,
	p.PayFrequency, t.Name
FROM EmployeePayHistory p 
INNER JOIN Employee e 
ON p.EmployeeID = e.EmployeeID
INNER JOIN Contact c 
ON e.ContactID = c.ContactID
INNER JOIN EmployeeDepartmentHistory ed 
ON ed.EmployeeID = e.EmployeeID
INNER JOIN Shift s 
ON s.ShiftID = ed.ShiftID
INNER JOIN Department d 
ON d.DepartmentID = ed.DepartmentID
INNER JOIN EmployeeAddress ea 
ON ea.EmployeeID = e.EmployeeID	
INNER JOIN Address a 
ON a.AddressID = ea.AddressID
INNER JOIN StateProvince sp 
ON sp.StateProvinceID = a.StateProvinceID
INNER JOIN SalesTerritory t 
ON sp.TerritoryID = t.TerritoryID
INNER JOIN
(
	SELECT e.LoginID, MAX(RateChangeDate) AS RateChangeDate
	FROM EmployeePayHistory p 
	INNER JOIN Employee e 
	ON p.EmployeeID = e.EmployeeID
	GROUP BY e.LoginID
) dt
ON e.LoginID = dt.LoginID AND p.RateChangeDate = dt.RateChangeDate
WHERE ed.EndDate IS NULL
ORDER BY e.EmployeeID

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

ral_ani
Starting Member

2 Posts

Posted - 2008-05-06 : 18:32:24

Awesome thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources