Q

Latest contract period for each employee

I have an MS Access table with employee information and contract periods. Each contract period has a unique ID, but may of course have the same employee number with different contract start date and contract end date. How can I use this table to find the last contract period for each employee? You can assume that one contract doesn't start until the previous one ends.

Assuming your table looks like this --

cID  cStart      cEnd        cEmpl
122  2002-01-01  2002-03-31  john
123  2002-01-01  2002-02-28  fred
124  2002-01-01  2002-09-30  bill
125  2002-04-01  2002-04-30  fred
126  2002-05-01  2002-05-31  fred
127  2002-04-01  2002-06-30  john
128  2002-11-01  2002-12-31  bill

The query must find the latest contract for each employee, and since one contract doesn't start until the previous one ends, we can use MAX(cStart). Using the start date rather than end date is preferable, since this allows ongoing contracts to have a null end date, although this might not apply in your situation.

However, that's not enough to give the entire contract period. You need both the start date and the end date. But how can you get the end date when selecting MAX(cStart)? The answer is to use a correlated subquery:

select cID, cStart, cEnd, cEmpl
  from yourtable XX
 where cStart = 
       ( select max(cStart)
           from yourtable
          where cEmpl = XX.cEmpl )

Here, the correlation is on the employee column. The outer query's WHERE clause accepts only those rows where cStart is the highest for that employee.

There's another way to do this, using a derived table in a join; see Rows having maximum group value in MySQL. Note that in MySQl, you have to do it in two steps using a temp table.


This was first published in November 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close