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
I can't complain but sometimes I still do - Joe Walsh