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? I prefere a solution that will work for all cases, so I can make the number of days backwards a variable in php.
Query 1
SELECT `_date`
FROM `daterange`
WHERE `trading_day` = '1' AND `_date` <= '2011-07-19'
ORDER BY `_date` DESC
LIMIT 1
OFFSET 1;
Query 2
SELECT `_date`
FROM `daterange`
WHERE `trading_day` = '1' AND `_date` <= '2011-08-01'
ORDER BY `_date` DESC
LIMIT 1
OFFSET 2;
Query 3
SELECT `_date`
FROM `daterange`
WHERE `trading_day` = '1' AND `_date` <= '2011-07-29'
ORDER BY `_date` DESC
LIMIT 1
OFFSET 3;
Prepared Statement (PDO Example)
$stmt = $dbh->prepare("SELECT `_date`
FROM `daterange`
WHERE `trading_day` = '1' AND `_date` <= :given_date
ORDER BY `_date` DESC
LIMIT 1
OFFSET :days_before");
$stmt->bindParam(':given_date', $given_date);
$stmt->bindParam(':days_before', $days_before);
$stmt->execute();
SELECT DATE(_date - INTERVAL trading_day DAY) FROM daterange;
This query will ignore when trading day is 0 and just return the date as it was. If you want to exclude dates where there is no trading day then you would want:
SELECT DATE(_date - INTERVAL trading_day DAY) FROM daterange WHERE trading_day!=0;
Also DATE() is not necessary here if your date field is already is format 2012-01-01. If your date field is a timestamp (2012-01-01 10:01:17) DATE() will return just the date part so it is like (2012-01-01). Inn other words this would also work for you equally based on what you have presented:
SELECT _date - INTERVAL trading_day DAY FROM daterange;
AND accordingly
SELECT _date - INTERVAL trading_day DAY FROM daterange WHERE trading_day!=0;
Your first example only works by accident right now :) You need to apply an order to the result set to make sure that you retrieve the largest date before 2011-07-19, and not just an arbitrary date.
SELECT _date
FROM daterange
WHERE trading_day <> 0 AND _date < '2011-07-19'
ORDER BY _date DESC
LIMIT 1
If what you want is not the first day before a given day, but some other day, then apply an OFFSET to your query as well.
This one will give you what you need for #3:
SELECT _date
FROM daterange
WHERE trading_day <> 0 AND _date < '2011-07-29'
ORDER BY _date DESC
LIMIT 1
OFFSET 2
The OFFSET 2 instructs MySQL to skip the first two results that would have been returned, and starts with the third one.
Select temp._date
FROM (Select _date FROM daterange WHERE trading_day<>0 AND trading_day < $date ORDER BY _date) as temp
ORDER BY _date LIMIT $days_before;
And get only the first record.
You can also change the ORDER BY to DESC and the where clause in the enclosed select if it is in the future.
I'm not entirely clear on your question - but it seems that you want to select the previous trading date, and how many days ago that was. I'm not sure if you want to do that for ALL dates in the table, or for any single date.
I'll assume it's for ALL dates in the table, because otherwise it's a very simple query.
SELECT
dr._date as the_date,
d2._date as previous_trading_date,
DATEDIFF(dr._date, d2._date) days_ago
FROM daterange dr
JOIN (
SELECT _date FROM daterange
WHERE trading_day > 0
ORDER BY _date DESC
) d2 ON d2._date < dr._date
GROUP BY dr._date
ORDER BY dr._date DESC
What this query does is select every date from daterange, and then runs a sub-query to select every date minus the ones that are not trading days, and sort them in descending order (most recent first). It joins every date onto every date that is previous to it, but we then group by the daterange._date to only get the first row (which is going to be the first date that is a trading day, since we did the sub-select with the order clause). We then use the DATEDIFF() function to count the # of days between the date and the previous trading date.
So, if you want to select a previous date which is some number ($x) of days before a given date ($date), and ignore dates which have a trading_day value of 0, you'd do this:
SELECT _date
FROM daterange
WHERE _date < '{$date}'
AND trading_day > 0
ORDER BY _date DESC
LIMIT 1
OFFSET {$x - 1}
The OFFSET clause allows you to skip the first {$x - 1} days which match your WHERE clause, which should arrive you to the day you want. This query makes some assumptions though. Primarily that you have one (and only one) row on daterange for every day. If you don't, then you'll end up with some unexpected results.
But after thinking about it, you could do something like this query:
SELECT _date
FROM daterange
WHERE _date <= DATE_SUB('2011-08-01', INTERVAL {$x} DAY)
AND trading_day > 0
ORDER BY _date DESC
LIMIT 1
This query does the date subtraction so you'll go back to the date {$x} days ago, but by adding the trading_day > 0 clause, it will remove any days that aren't a trading day, order the results in descending date order, and select the first one - that should be the date you want. Try it out.