Create query based on a field that won't be the same value in both tables

[Jim G]

I have two tables: TestA and TestB. Both tables have 3 fields: ID,
Name, and RunDate. I need to create a query which will join the two
tables first on Name but then I need to match up the RunDates even
though the RunDates won't be the same.

CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDate
DATETIME)
CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDate
DATETIME)

INSERT INTO TestA VALUES ('Account 1', '9/1/2004 12:00PM')
INSERT INTO TestB VALUES ('Account 1', '9/1/2004 12:15PM')
INSERT INTO TestA VALUES ('Account 1', '9/2/2004 1:00PM')
INSERT INTO TestB VALUES ('Account 1', '9/2/2004 1:15PM')
INSERT INTO TestA VALUES ('Account 1', '9/3/2004 3:00PM')
INSERT INTO TestA VALUES ('Account 2', '9/5/2004 4:00PM')
INSERT INTO TestB VALUES ('Account 2', '9/5/2004 4:15PM')
Here's a common scenario:
User updates TestA data for Account 1 on 9/1/2004 at 12:00pm. Then
the user updates TestB data for Account 1, 15 minutes later. I want
these two records to match. The user must always update TestA data
before they update TestB data. Therefore, there might be more rows in
TestA then in TestB

Here's what the results should look like for the above data.

Name TestA Date TestB Date
---- ---------- ----------
Account 1 9/1/2004 12:00pm 9/1/2004 12:15PM
Account 1 9/2/2004 1:00pm 9/2/2004 1:15PM
Account 1 9/3/2004 3:00pm (NULL)
Account 2 9/5/2004 4:00pm 9/5/2004 4:15PM

Any help would be much appreciated!!!!

[Hugo Kornelis]

On 29 Sep 2004 07:41:18 -0700, Jim G wrote:

Here's what the results should look like for the above data.

Name TestA Date TestB Date
---- ---------- ----------
Account 1 9/1/2004 12:00pm 9/1/2004 12:15PM
Account 1 9/2/2004 1:00pm 9/2/2004 1:15PM
Account 1 9/3/2004 3:00pm (NULL)
Account 2 9/5/2004 4:00pm 9/5/2004 4:15PM

Hi Jim,

Thanks for posting DDL ans INSERTS for sample data!

The following query gives the above results:

SELECT a.Name, a.RunDate, b.RunDate
FROM TestA AS a
LEFT JOIN TestB AS b
ON b.Name = a.Name
AND b.RunDate >= a.RunDate
AND NOT EXISTS (SELECT *
FROM TestA AS a2
WHERE a2.Name = a.Name
AND a2.RunDate > a.RunDate
AND a2.RunDate < b.RunDate)
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Jim G]

Awesome! That worked perfectly. Thanks!