Hi all.
I have problem with this query.
In my table mysql tbl_d
the value of the field myDate
is 2011-05-23.
SELECT d.LOCK, d.myDate, x.dt
FROM tbl_d AS d
CROSS JOIN (
SELECT
CASE
#case 1
WHEN SUM(CASE WHEN MONTH(myDate) = MONTH(CURRENT_DATE)
AND YEAR(myDate) = YEAR(CURRENT_DATE) THEN 1 ELSE 0 END)
> 0 THEN CURRENT_DATE
#case 2
WHEN SUM(CASE WHEN MONTH(myDate) = MONTH(CURRENT_DATE -1)
AND YEAR(myDate) = YEAR(CURRENT_DATE) THEN 1 ELSE 0 END)
> 0 THEN CURRENT_DATE - INTERVAL 1 MONTH
#case 3
WHEN SUM(CASE WHEN MONTH(myDate) = MONTH(CURRENT_DATE -2)
AND YEAR(myDate) = YEAR(CURRENT_DATE) THEN 1 ELSE 0 END)
> 0 THEN CURRENT_DATE - INTERVAL 2 MONTH
#last case
ELSE CURRENT_DATE - INTERVAL 3 MONTH
END AS dt
FROM tbl_d) AS x
WHERE 1
AND MONTH(d.myDate) = MONTH(x.dt)
AND YEAR(d.myDate) = YEAR(x.dt)
GROUP BY d.LOCK
ORDER BY d.LOCK ASC
The sql debug:
#case 1 —> 5 = 7 false;
#case 2 —> 5 = 6 false;
#case 3 —> 5 = 5 true;
Affected rows: 0
Time: 0.110ms
Why? Always in the #last case?
Thanks in advance, any help is very appreciated.