Select past date from database x days from now

Posted by Pr0no on Stack Overflow See other posts from Stack Overflow or by Pr0no
Published on 2012-08-30T15:37:09Z Indexed on 2012/08/30 15:38 UTC
Read the original article Hit count: 142

Filed under:

Consider the following table daterange

_date        trading_day
------------------------
2011-08-01   1
2011-07-31   0
2011-07-30   0
2011-07-29   1
2011-07-28   1
2011-07-27   1
2011-07-26   1
2011-07-25   1
2011-07-24   0
2011-07-23   0
2011-07-22   1
2011-07-21   1
2011-07-20   1
2011-07-19   1
2011-07-18   1
2011-07-17   0

I'm in need of a query that returns a _date, x days before a given _date. When counting back, _days with trading_day = 0 should be ignored. A few examples:

input                    | output
-------------------------+------------
1 day  before 2011-07-19 | 2011-07-18
2 days before 2011-08-01 | 2011-07-28 (trading_day = 0 don't count)
3 days before 2011-07-29 | 2001-07-26

The first one is easy:

SELECT _date 
FROM daterange 
WHERE trading_day = 0 AND _date < '2011-07-19' LIMIT 1

But I don't know how to query for the other examples. Do you?

© Stack Overflow or respective owner

Related posts about mysql