Using Join To Match Transactions To Client Data

I am running SQL2k on Win2k sp1. I need to match a transaction record (using txn_date) to client data as it would have appeared on the date of the transaction (using client_last_update).

A new row is inserted into my client table every time any part of the clients information changes, thereby leaving the change history in tact. The client_last_update field holds the date the change took place. It is required that the user be provided the client information that was current on the day of the transaction.

Example: A client record (name, address, accountno) was inserted to the client table on 2/1/2000 (the last_update field is empty because it's a new record).

The client record is modified on 4/15/2000 for a name change (making the last_update = 4/15/2000), and again on 12/5/2000 (making the last_update = 12/5/2000).

Transactions are made by the client on 2/5/2000, 6/10/2000, and 3/25/2001. These dates are held in the txn_date field in the transaction table.

The client now has 3 transaction records in the transaction table for his/her account number and 3 client records in the client table.

The transaction on 2/5/2000 should be joined to the initial client record ((2/5/2000 > empty) AND (2/5/2000 < 4/15/2000))

The transaction on 6/10/2000 should be joined to the client record having the last_update = 4/15/2000 (6/10/2000 > 4/15/2000) AND (6/10/2000 < 12/5/2000))

The last transaction, executed on 3/25/2001 should be joined to the most current client record, which has last_update = 12/5/2000 (3/25/2001 > 12/5/2000).

I have been experimenting with the code below, and found it to only return client data where the client record update_date = '4/18/2000'.

select * from transaction t Left Outer Join client c On t.SEC_ACCT = c.Account_No and t.Txn_Date > coalesce(c.Last_Update_Date, '1/1/1900') and t.Txn_Date < (select min(c2.Last_Update_Date) from client c2 where c2.Last_Update_Date > coalesce(c.Last_Update_Date, '1/1/1900'))

An example of a record that DID get returned carried a txn_date of '1/2/2001', and possible client update_dates of: NULL, '5/13/1999', and '4/18/2000'. The row returned carried a client update_date of '4/18/2001', and rightly so.

An example of a record that DID NOT get returned carried a txn_date = '1/2/2001' and possible client update_dates of: NULL, and '6/11/1999. This row was not returned.