Find most current date

[ucmeknot]

I have two tables. Table 1 lists product codes, month and volume in lbs. Table 2 lists product codes, month and average sales price. A product in Table 1 may not have an average sales price in Table 2 for that month, but would have a sales price listed for some prior month. What I'm looking for is a query that would let me select all the products from Table 1 for a given month i.e. May 2003 and the most current sales price for that product from Table 2. The one restriction is that the sales price selected from Table 2 can't be from a month more current than the month selected for Table 1. For example let's say product 1001 has an average sales price in Table 2 for Jan 03, Feb 03 and Apr 03 but not Mar 03. The table below represents the result set I would expect based on the month selected in Table 1:

Volume Avg Sales
Month Price Month
Table 1 Table 2
Jan 03 Jan 03
Feb 03 Feb 03
Mar 03 Feb 03
Apr 03 Apr 03

I've tried every combination of the MAX function I could think of but nothing works the way I want. I'm sure someone out there knows a solution for this.

 

[Nouba]

try a query like

SELECT
  T1.PCODE
, T1.SMONTH
, T1.VOL
, (SELECT TOP 1
    APRICE
  FROM Table2
  WHERE SMONTH<=T1.SMONTH
  ORDER BY SMONTH DESC) AS AVGPRICE
FROM Table1 AS T1