Selecting the Earliest Record for each client?

[jaybar48]

I have two databases. Database A is the Client Database. Database B is the enrollment database. Each client can have multiple enrollment records. Database A is linked to Database B via the client_ID field.

I wan't to do the following and I don't know SQL.

1) I want to select the earliest enrollment record for each client.

2)I want to base all my running totals on this subset of records.

3) I want to further exclude records if the earliest enrollment date for that client falls outside of a date ramge

I seem to have gotten part of the way. If I group on B.client_id and then use the group selection formula
B.enrollment_date=minimum(b.enrollment_date,B client_id) I get only those records with the minimum enrollment date for each client. I want to further exclude records with minimum enrollment dates that fall outside a specified date range.

For Example:

Client # Minimum Enrollment Date

1001 1/1/2003
1002 1/1/2001
1003 1/1/2000

How would I then be able to exclude (completely-not just not display it), the record for client 1003, if I wanted to limit the minimum enrollment dates to not earlier than 2001.





How can I do this? Can this be done with running totals?

Thanks in advance,


Jay

 

[lbass]

Just amend your group select to:

{b.enrollment_date} = minimum({b.enrollment_date},{b. client_id}) and
minimum({b.enrollment_date}, {b.client_id}) >= Date(2001, 01, 01)

-LB