Join query

Join query

• Verna

  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.
  
  

• cccedi

  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

  
  

• Joe Bourne

  What you want is described in my request http://sqljunkies.com/WebLog/simons/archive/2006/02/06/Upper_join_request.aspx
  
  

• C l e m e n s

  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.
  

• Steen L. Nielsen

  can you not use the maintdate as the to date
  

• mhdostal

  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

  
  
  

• JennyW

  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

  
  
  
• Join query