Simple Annoying 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

SQL Server 2005 Forums

Transact-SQL (2005)

Simple Annoying Query

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2009-03-11 : 11:34:53

Hello

I'm trying to write quite a simple query, but i'm struggling with the code.

I have a table holding employee job details both current and historic:

NAME ID JOB_DESCRIPTION END_DATE
Dean 01 Administrator_____ 01/08/08
Mack 02 Administrator_____ 01/03/09
Mack 02 Senior_Admin_____ NULL
Tash 03 Administrator_____ 07/02/09
Boon 04 Administrator_____ 04/01/09
Boon 04 Accountant_______ NULL

All I want is the one entry for each employee, either the latest post entry i.e current position or if they have no current post the last one they held i.e i want the results to look like this:

NAME ID JOB_DESCRIPTION END_DATE
Dean 01 Administrator_____ 01/08/08
Mack 02 Senior_Admin____ NULL
Tash 03 Administrator_____ 07/02/09
Boon 04 Accountant_____ NULL

I'm sure this is relatively simple but i'm struggling for some reason...!!

Can anybody help

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 11:39:12

What do you have to define "latest post entry"?

E 12°55'05.63"
N 56°04'39.26"

bendertez
Yak Posting Veteran

94 Posts

Posted - 2009-03-11 : 11:46:15

Hi Peso

yes the latest or last post (if the employee has left).

I don't want any historic post details for current employees

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 11:49:30

Among the sample records above, what do you have to define "latest post entry", ie physical order?

E 12°55'05.63"
N 56°04'39.26"

bendertez
Yak Posting Veteran

94 Posts

Posted - 2009-03-11 : 11:52:02

yes, any post that has a null end date (current) or any person who has left with their last post position.

hope this helps

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-11 : 11:53:49

Can you try this..

declare @t table (jobid int,descr char(30),end_date datetime)
insert @t
select 1,'admin','01/01/2008' union all
select 2,'senioradmin','01/01/2008' union all
select 2,'senioradmin',null union all
select 3,'admin','05/01/2008' union all
select 4,'admin','01/01/2008' union all
select 4,'acct',null

select * from
(select row_number() over (partition by jobid order by isnull(end_date,'12/21/9999') desc) as rn, * from @t ) t
where t.rn = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources