First Record Join

[scmhc]

I need to join two tables. The First Table Has a ClientID and an Evaluation Date.
The Second Table is a Many-to-One relationship and Has the ClientID and a Effective Date. What I want is a join of the First and Second Table where I only get the first record in the Second Table where Effective Date is Less than or equal to the Evaluation Date.

Table 1:
Client1, 2006-12-31
Client2, 2007-01-10
Client3, 2006-10-20

Table 2:
Client1, 2007-01-08
Client1, 2006-12-31
Client1, 2006-11-30
Client2, 2007-01-12
Client2, 2006-12-20
Client2, 2006-11-11
Client3, 2007-01-08

Results:
Client1, 2006-12-31, Client1, 2006-12-31
Client2, 2007-01-10, Client2, 2006-12-20
Client3, 2006-10-20, Null, Null

Thanks for your help

[MMcCarthy]

I need to join two tables. The First Table Has a ClientID and an Evaluation Date.
The Second Table is a Many-to-One relationship and Has the ClientID and a Effective Date. What I want is a join of the First and Second Table where I only get the first record in the Second Table where Effective Date is Less than or equal to the Evaluation Date.

Table 1:
Client1, 2006-12-31
Client2, 2007-01-10
Client3, 2006-10-20

Table 2:
Client1, 2007-01-08
Client1, 2006-12-31
Client1, 2006-11-30
Client2, 2007-01-12
Client2, 2006-12-20
Client2, 2006-11-11
Client3, 2007-01-08

Results:
Client1, 2006-12-31, Client1, 2006-12-31
Client2, 2007-01-10, Client2, 2006-12-20
Client3, 2006-10-20, Null, Null

Thanks for your help

Please post the SQL of the query you are currently using.

[scmhc]

he code so far is as follows:

SELECT [PERF - from Tier (Modified)].CLIENTID,
[PERF - from Tier (Modified)].SITEID,
[PERF - from Tier (Modified)].PURPEVAL,
[PERF - from Tier (Modified)].EVALDATE,
[PERF - from Tier (Modified)].INITEVADA,
[PERF - PurpEval 01 All].EVALDATE

FROM [PERF - from Tier (Modified)] INNER JOIN [PERF - PurpEval 01 All]
ON ([PERF - from Tier (Modified)].SITEID = [PERF - PurpEval 01 All].SITEID) AND
([PERF - from Tier (Modified)].CLIENTID = [PERF - PurpEval 01 All].CLIENTID)

WHERE ((([PERF - PurpEval 01 All].EVALDATE)<=[PERF - from Tier (Modified)]![EVALDATE]));


This gives me all records with date <= Modified evaldate
I ONLY WANT THE FIRST RECORD <= Modified evaldate

[NeoPa]

Try this as a basis :

SELECT T1.ClientID,
       T1.EvaluationDate,
       Min(T2.EffectiveDate) AS MinEffDate
FROM Table1 AS T1 LEFT JOIN Table2 AS T2
  ON T1.ClientID=T2.ClientID
WHERE T2.EffectiveDate<T1.EvaluationDate
GROUP BY T1.ClientID