Select from table only displaying current data

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)

Select from table only displaying current data

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2008-11-17 : 09:15:28

Hello

I want to select from a employee post table to give me the latest or last employee post data.

If a person has one current post the I want to see the post end date as NULL, If a person has left a post and not started another post I just want to see the post end date field with the date they left.

If a person has Left a post and then gone on to be current in another post I want to see a NULL value in the end date field. Howevere what I keep returning instead of the null value is the end date of the previous post.

All I want is the latest data for the end date field if they are in a current post I want to see a NUILL value.

My table is called EMPLOYEE and the fields I'm using are EMP_NO, START_DATE, END_DATE.

There can be multiple post for the same employee, but I only want the current position on the END_DATE field whether that be a date as they have no current post or a NULL value as they are still employed in a post. I don't want the employee details repeated in my results showing all posts.

Can anyone help

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 09:21:54

SELECT EMP_NO, START_DATE, END_DATE
FROM Employee
WHERE END_DATE IS NULL
UNION ALL
SELECT EMP_NO, START_DATE, END_DATE
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY EMP_NO ORDER BY START_DATE DESC) AS Seq,EMP_NO, START_DATE, END_DATE
FROM Employee e1
WHERE END_DATE IS NOT NULL
)t
WHERE t.Seq=1

bendertez
Yak Posting Veteran

94 Posts

Posted - 2008-11-17 : 10:08:15

Hello Visakh

Thanks for the reply.

Unfortunately this still seems to show all the results for the end date and not just the current end date.

If I had an employee with two posts, one post with an end date and one null value(current), I would want to the current post i.e. the NULL Value.

However if there was an employee who left a post and didn't start another (i.e. left the organisation) i would want to see the end_date field with their end date entered.

At the moment i'm getting all post records being displayed.

Any other ideas ?

Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 11:34:07

quote:
Originally posted by bendertez

Hello Visakh

Thanks for the reply.

Unfortunately this still seems to show all the results for the end date and not just the current end date.

If I had an employee with two posts, one post with an end date and one null value(current), I would want to the current post i.e. the NULL Value.

However if there was an employee who left a post and didn't start another (i.e. left the organisation) i would want to see the end_date field with their end date entered.

At the moment i'm getting all post records being displayed.

Any other ideas ?

Thanks again.

i cant understand your scenario unless i see some data. can you post some sample data and explain what you want giving reqd output.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources