Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
May 14, 2004
108
US


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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top