records between dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tallymike - 31 Dec 2005 14:56 GMT

I have a large database of contracts that includes: fields = SSN, Yr, and the
like. Years span from 1999 to 2005. Each contract record includes an
individual's SSN. However, most individuals have had multiple contracts,
some in different years, some in the same year.
I need to find individuals (SSN) by first instance. For example,
individuals that are found in year 2000 but NOT in 1999, even though they
also may be found in 2001 or later. The final result I need is a listing of
records by SSN that are between X and Y years but not before X year.

Reply to this Message

John Spencer - 31 Dec 2005 15:17 GMT

To get the list of SSN and Yr you would need a query like the following.

SELECT SSN, Min(Yr) as FirstYr
FROM [Your Contracts Table]
WHERE Yr Between 2001 and 2003
GROUP BY SSN

If you wanted more details then that, you could use a coordinated sub-query.

SELECT * FROM
FROM [Your Contracts Table]
WHERE Yr =
(SELECT Min(yr)
FROM [Your Contracts Table] as Temp
WHERE Temp.Yr Between 2001 and 2003
AND TEMP.SSN = [Your Contracts Table].SSN)

> I have a large database of contracts that includes: fields = SSN, Yr, and the
> like. Years span from 1999 to 2005. Each contract record includes an
[quoted text clipped - 4 lines]
> also may be found in 2001 or later. The final result I need is a listing of
> records by SSN that are between X and Y years but not before X year.

Reply to this Message