Select past date from database x days from now
- by Pr0no
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?
