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