INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Select distinct records based on max(date) or NULL date

Select distinct records based on max(date) or NULL date

(OP)


I am trying to get a list of employees based on their employee status or their most recent termination date. If the employee is active, the termination date will be '1700-01-01', should be NULL but Oracle returns the '1700-01-01'. There are also employees that have worked in multiple companies within our organization, I only want the record from the most recent company, whether active or terminated. An employee may also have different Employee numbers in the different companies, so the selection will have to be based on the SSN (Fica) number.

Here is an original data set:

company employee Fica First_name emp_status Term_date
5 7026 Jason T1 2013-09-16 00:00:00.000
500 7026 Jason T1 2010-11-30 00:00:00.000
7 7026 Jason T1 2009-07-31 00:00:00.000
2 90908 Jason A1 NULL
505 293866 William T1 2008-05-23 00:00:00.000
7 7243 Ashley T1 2010-07-11 00:00:00.000
2 90478 Michael T1 2013-01-11 00:00:00.000
500 90478 Michael T1 2011-09-26 00:00:00.000
500 311002 Andreas A1 NULL
3 365463 Matthew A1 NULL
500 248766 Chris T1 2007-04-23 00:00:00.000
500 90692 Kaitlyn T1 2012-03-13 00:00:00.000
2 90692 Kaitlyn A5 NULL
500 90236 Jeff T1 2011-09-26 00:00:00.000
2 90236 Jeff A1 NULL
2 90433 Nathan T1 2012-03-26 00:00:00.000
500 90433 Nathan T1 2011-09-26 00:00:00.000

Here are the results I am trying to get:

company employee Fica First_name emp_status Term_date
2 90908 Jason A1 NULL
505 293866 William T1 2008-05-23 00:00:00.000
7 7243 Ashley T1 2010-07-11 00:00:00.000
2 90478 Michael T1 2013-01-11 00:00:00.000
500 311002 Andreas A1 NULL
3 365463 Matthew A1 NULL
500 248766 Chris T1 2007-04-23 00:00:00.000
2 90692 Kaitlyn A5 NULL
2 90236 Jeff A1 NULL
2 90433 Nathan T1 2012-03-26 00:00:00.000

Thanks for any help you are able to give. I need to run this on a SQL2005 server which will be connecting to an Oracle server via ODBC.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close