I have two tables. The first table has two columns: ID and date_of_price. The date_of_price field skips weekend days and bank holidays when markets are closed.

table: trading_dates
ID    date_of_price
1     8/7/2008
2     8/8/2008
3     8/11/2008
4     8/12/2008

The second table also has two columns: ID and expiration_date. The expiration_date field is the one day in each month when options expire.

table: expiration_dates
ID    expiration_date
1     9/20/2008
2     10/18/2008
3     11/22/2008

I would like to do a query that subtracts a certain number of days from the expiration dates, with the caveat that the resulting date must be a valid date_of_price. If not, then the result should be the next valid date_of_price.

For instance, say we are subtracting 41 days from the expiration_date. 41 days prior to 9/20/2008 is 8/10/2008. Since 8/10/2008 is not a valid date_of_price, we have to skip 8/10/2008. The query should return 8/11/2008, which is the next valid date_of_price.

Any advice would be appreciated! :-)