By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,264 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

First Record Join

P: 2
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
Jan 15 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Jan 15 '07 #2

P: 2
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
Jan 15 '07 #3

NeoPa
Expert Mod 15k+
P: 31,494
Try this as a basis :
Expand|Select|Wrap|Line Numbers
  1. SELECT T1.ClientID,
  2.        T1.EvaluationDate,
  3.        Min(T2.EffectiveDate) AS MinEffDate
  4. FROM Table1 AS T1 LEFT JOIN Table2 AS T2
  5.   ON T1.ClientID=T2.ClientID
  6. WHERE T2.EffectiveDate<T1.EvaluationDate
  7. GROUP BY T1.ClientID
Jan 16 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.