making sure "expiration_date - X" falls on a valid "date_of_price" (if not, use the next valid date_of_price)

Question

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! :-)

Answer 1

This will subtract 41 days from the date with ID = 1 in expirations_dates and find the nearest date_of_price.

Query

Modify the ID and the 41 for different dates.

SELECT date_of_price 
FROM trading_dates 
WHERE date_of_price >= (
    SELECT DATE_SUB(expiration_date, INTERVAL 41 DAY)
    FROM expiration_dates
    WHERE ID=1
) 
ORDER BY date_of_price ASC 
LIMIT 1;

Performance

To get the best performance from this query, your trading_dates table should have a clustered index on date_of_price (this will make the ORDER BY a no-op) and of course a primary key index on expirations_dates.ID (to lookup the expiration date quickly).

Don't put in the clustered index blindly though. If you update or insert values more often than you look up expirations like this, then don't put it in since all your inserts/updates will have an added overhead to keep the clustered index.