Join query

I have two tables Table1 and Table2

Table1 columns:
CustomerNum varchar(12)
InvDate smalldatetime

Table2 columns
CustomerNum varchar(12)
MaintDate smalldatetime
Dis float

Table1 Data (sample):
CustomerNum InvDate
995169 2/4/2002
995169 11/4/2002
995169 1/1/2003
995169 3/4/2003
995169 10/4/2003
995169 1/4/2005
995169 5/4/2005
995169 11/15/2005

Table2 Data (sample):
CustomerNum MaintDate Dis
995169 10/3/2001 1.07
995169 10/3/2002 1.1
995169 5/16/2003 1.7
995169 9/30/2003 2.9
995169 1/1/2005 2.8
995169 3/31/2005 2.95
995169 10/31/2005 2.85

I want to display customerNum, InvDate from Table1 along with applicable Dis from Table2. Dis for a customer changes form time to time. We need to pick the right one based on Table1.InvDate and Table2.MaintDate. For example, the applicable Dis for customner 995169 invoice date 2/4/2002 is 1.07
After joining the tables, I want the data to be displayed as under:

CustomerNum InvDate Dis
995169 2/4/2002 1.07
995169 11/4/2002 1.1
995169 1/1/2003 1.1
995169 3/4/2003 1.7
995169 10/4/2003 2.9
995169 1/4/2005 2.8
995169 5/4/2005 2.95
995169 11/15/2005 2.85

Any help in constructing sql qury will be appreciated. Thanks in advance.

do you want the closest invdate from table 1 that is before maintdate for each customer. it is not clear how you are wanting the data joined, please explain further.|||What I want is get the applicable Dis from Table2 for the customer's invoice date. So after joining the table2 to table1, I should display table1.customernum, table1.invdate, table2.Dis.|||

Lots of subquery hell unless you add a todate on to your second table.

You can then join where invdate between table2.fromdate and table2.todate

|||What you want is described in my request http://sqljunkies.com/WebLog/simons/archive/2006/02/06/Upper_join_request.aspx|||can you not use the maintdate as the to date?|||

try:

SELECT sub.CustomerNum, sub.invdate, table2.Dis
FROM table2
INNER JOIN
(
SELECT t1.CustomerNum, MAX(t1.invdate) invdate, t2.maintdate
FROM table2 t2
JOIN table1 t1
ON t1.InvDate < t2.maintdate
AND t1.CustomerNum = t2.CustomerNum
GROUP BY t1.CustomerNum, t2.maintdate
) AS sub
ON table2.CustomerNum = sub.CustomerNum
AND table2.maintdate = sub.maintdate

|||

You can do below in SQL Server 2000 onwards:

select t1.CustomerNum, t1.InvDate

, (select top 1 t2.Dis from table2 as t2

where t2.CustomerNum = t1.CustomerNum and t2.MaintDate <= t1.InvDate

order by t2.MaintDate desc) as Dis

from table1 as t1