MAX(date) Row For Each Person - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

MAX(date) Row For Each Person

Author

Topic

pcgirl365
Starting Member

5 Posts

Posted - 2007-11-08 : 11:28:06

Hello,

I have a view containing personidno,payrate,effective date for a population of employees. I want to pull the personidno and pay rate for each person where the effectivedate is greatest.

I was using the query:
Select v.personidno, v.payrate, vpriorpayrate v where v.fromeffectdate = (select max(s.fromeffectdate) from vpriorpayrate s where s.fromeffecdate = v.fromeffectdate)

Ultimately, each employee has multiple rows depending on how many rates they have had over the years; I want to return the rate and personidno for each person where the from date is the max date for that person. I continue to get all the rows for each employee. When I leave off my 2nd where statement, I then only get 1 row - the employee with the highest from date in the entire view.

Any suggestions are appreciated

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-11-08 : 13:19:13

Try something like this:


Select A.* 
From YourTable A
JOIN (
	Select PersonIdNo, Max(effectivedate) as MDate
	FROM YourTable
	Group by PersonIdNo
	) B ON A.PersonIdno = B.PersonIdno AND A.Effectivedate = B.Mdate

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

pcgirl365
Starting Member

5 Posts

Posted - 2007-11-08 : 13:47:18

Thanks, I got it to work using something similar bringing in A.personidno = B.Personidno at the end of my query in a where statement; I was then able to get results per person, which is exactly what I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources