First Record Join 1

[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
Client2, 2006-12-05
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
Client2, 2006-12-05, Client2, 2006-11-11
Client3, 2006-10-20, Null, Null

The 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

Thanks for your help.
Steve

 

[PHV]

Perhaps something like this (typed, untested):
SELECT A.CLIENTID, A.SITEID, A.PURPEVAL, A.EVALDATE, A.INITEVADA, B.theDate
FROM [PERF - from Tier (Modified)] AS A LEFT JOIN (
SELECT CLIENTID, SITEID, MAX(EVALDATE) A theDate FROM [PERF - PurpEval 01 All]
WHERE EVALDATE<=A.EVALDATE AND SITEID = A.SITEID AND CLIENTID = CLIENTID
GROUP BY CLIENTID, SITEID
) AS B ON A.SITEID = B.SITEID AND A.CLIENTID = B.CLIENTID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[scmhc]

I actually did the same approach using two seperate queries and it worked. I will try it in a single query. Thanks for your help, I truly appreciate it.

Not real good at SQL statements YET.
Steve