Paul,

Try

SELECT monthenddate, 
  (SELECT MAX(tradedate) FROM trans tr
      where tr.assetid = @assetid 
        AND tr.tradedate <= med.monthenddate ) AS maxtradedate
  FROM monthdates med

>I have a table of month ending dates (monthdates), a table of assets (PK nassetid) and a transaction table (trans) which contains transactions for those assets and holds a FK to the asset table. I want to pull month ending dates for a given date range and at the same time find the max transaction date for a given assetid that is < = each monthend date I pull.
>
>In English here is what I am trying to do:
>
>SELECT all the monthending dates from the monthdates table where the monthendate is between a startdate and an enddate and for each of those resulting dates find the maximum tradedate from the transaction table where the tradedate < = each monthending date and the transaction assetid = a specific assetid
>
>Example of what I would like the result set to look like:
>
>monthenddate maxtradedate
>1999-12-31 1999-12-26
>2000-01-31 2000-01-15
>2000-02-28 2000-02-02
>2000-03-31 2000-02-02
>2000-04-30 2000-04-05
>
>Im having a hard time picturing the right way to formulate this query. Any suggestions?
>
>Paul Montgomery