Which Join criteria?

Transact-SQL
1

Consider the following 2 tables

table1:
PeriodYear | PeriodMonth | Encounter | Station | Trans_Date
2012 | 1 | J2 | DEL | 2012-04-01
2012 | 1 | J2 | 3OB | 2012-04-01
2012 | 1 | J2 | 3OB | 2012-04-03

table2:
PeriodYear | PeriodMonth | Encounter | Station | Trans_Date
2012 | 1 | J2 | 3OB | 2012-04-01
2012 | 1 | J2 | 3OB | 2012-04-02

I need to pull the records from table2 with the Trans_Date that is not in table1. In this particular case, my result set should be a single row as follows:

PeriodYear | PeriodMonth | Encounter | Station | Trans_Date
2012 | 1 | J2 | 3OB | 2012-04-02

The Join criteria is Trans_Date in table2 must be between MIN(Trans_Date) and MAX(Trans_Date) at the Encounter level in table1.

I hope this makes sense. If anybody can shed some light that would be very much appreciated.

Thank you!

2

FROM table2 t2 LEFT JOIN table1 t1 ON t2.Encounter=t1.Encounter
AND t2.Trans_Date=t1.Trans_Date

WHERE t1.Encounter IS NULL

The join above works because in my example I only have 2 records in table2. Please consider millions of other records with different values in all other fields.

Thanks again!

3

SELECT PeriodYear,PeriodMonth,Encounter,Station,TransDate
from ##T2 WHERE TransDate =(

select TransDate from ##T2
except
select TransDate from ##T1
)