Effective data query - 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

General SQL Server Forums

New to SQL Server Programming

Effective data query

Author

Topic

ymcglawn
Starting Member

4 Posts

Posted - 2009-03-24 : 14:21:22

I am new to sql and want to write a query that will allow me to pull one records per employee. Below are the fields that are required, but each employee has multiple records per effective. Want only the latest records. Any help on retrieving this data would be greatly appreciated. Here is the query that I executed with the results.

SELECT XHR_JobHistory.EffectiveDate, XHR_JobHistory.EmpId, XHR_JobHistory.JobId, XHR_JobHistory.JobTitle, XHR_JobHistory.Status, XHR_JobHistory.SeqNbr
FROM Ansco.dbo.XHR_JobHistory XHR_JobHistory
WHERE (XHR_JobHistory.Status='A')
ORDER BY XHR_JobHistory.EmpId

EffectiveDate EmpId JobId JobTitle Status SeqNbr
04/01/08 0:00 000437 114 Mechanic A 1
04/01/08 0:00 000479 177 Benefits Admin A 1
03/01/09 0:00 000479 178 Generalist A 2
04/01/08 0:00 000485 115 Foreman A 1
04/01/08 0:00 000509 115 Foreman A 1
04/01/08 0:00 000511 115 Foreman A 1
04/01/08 0:00 000518 109 Supervisor A 1
04/12/04 0:00 000518 109 Supervisor A 2
04/01/08 0:00 000537 115 Foreman A 1
04/01/08 0:00 000540 115 Foreman A 1
04/01/08 0:00 000566 115 Foreman A 1

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-24 : 14:25:57

Are you using SQL server 2005 or higher?

ymcglawn
Starting Member

4 Posts

Posted - 2009-03-24 : 14:42:00

I am using sql server 2005

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-24 : 15:14:46

Can you try this...

SELECT D.EffectiveDate, D.EmpId, D.JobId, D.JobTitle, D.Status, D.SeqNbr
FROM (
SELECT EffectiveDate,EmpId,JobId,JobTitle,Status,SeqNbr,ROW_NUMBER () OVER(PARTITION BY EmpId ORDER BY EffectiveDate DESC) AS Rn
FROM
Ansco.dbo.XHR_JobHistory XHR_JobHistory
WHERE (XHR_JobHistory.Status='A')
) D
WHERE D.Rn = 1
ORDER BY D.EmpId

ymcglawn
Starting Member

4 Posts

Posted - 2009-03-24 : 15:40:20

Thanks....... that worked

... Don't understand how. Can you recommend a resource that I could get to help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-25 : 13:20:02

see this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources