Problem with query MONTH(`_Dates`)

[Miguel61]

Hi.

I need with this query that when the current month in the field `_Dates` is null, where condition change to the previous month to the current.

This query is working but you can optimize it?

SELECT `_Dates` 
   FROM _tableDates
      WHERE
         MONTH(`_Dates`) = MONTH(`_Dates`())
         OR MONTH(`_Dates`) = MONTH(`_Dates`)-1 

[transio]

Something like this?

UPDATE tableDates SET _Dates = CURRENT_DATE - INTERVAL 1 MONTH WHERE _Dates IS NULL;

[Miguel61]

Something like this?

UPDATE tableDates SET _Dates = CURRENT_DATE - INTERVAL 1 MONTH WHERE _Dates IS NULL;

Thanks Sir, but I need select `_Dates` value.. not update `_Dates` value...

_tableDates example #1

ID	_DATES		NUMBER
1	2010-02-01	33
2	2010-02-02	36

In this table I don't have `_Dates` with current month (3) and I need select current month -1 (2).

_tableDates example #2

ID	_DATES		NUMBER
1	2010-02-01	33
2	2010-02-28	36
3	2010-03-01	36

In this table I have `_Dates` with current month (3) and I need select current month (3).

Can you help me?

[transio]

What you're looking for is a conditional select... if the count of the current month (3) returns 0 records, you want to select the past month's (2) records.

This should work, but it's not optimal (nor tested... there may be some bugs):

SELECT d.*
FROM _tableDates AS d
    CROSS JOIN (
        SELECT 
            CASE 
                WHEN SUM(CASE 
                    WHEN MONTH(_Dates) = MONTH(CURRENT_DATE) 
                        AND YEAR(_Dates) = YEAR(CURRENT_DATE) 
                    THEN 1
                    ELSE 0 END) > 0 
                THEN CURRENT_DATE 
                ELSE CURRENT_DATE - INTERVAL 1 MONTH 
            END AS dt 
        FROM _tableDates) AS x
WHERE MONTH(d._Dates) = MONTH(x.dt) 
    AND YEAR(d._Dates) = YEAR(x.dt)

(Ok, tested, edited, and confirmed working...)

[Miguel61]

Thanks Sir, but I have this error in your query:

[SQL] SELECT d.*
FROM _tableDates AS d
    CROSS JOIN (
        SELECT 
            CASE 
                WHEN SUM(CASE 
                    WHEN MONTH(_Dates) = MONTH(CURRENT_DATE) 
                        AND YEAR(_Dates_ = YEAR(CURRENT_DATE) 
                    THEN 1
                    ELSE 0 END) > 0 
                THEN CURRENT_DATE 
                ELSE CURRENT_DATE - INTERVAL 1 MONTH 
            END AS dt 
        FROM _tableDates) AS x
WHERE MONTH(d._Dates) = MONTH(x.dt) 
    AND YEAR(d._Dates_ = YEAR(x.dt)
GROUP BY d.ID

[Err] 1064 - You have an error in your SQL syntax; check 
the manual that corresponds to your MySQL server version 
for the right syntax to use near 'THEN 1
                    ELSE 0 END) > 0 
                THEN CURRENT_DATE ' at line 9 

[transio]

Fixed:

SELECT d.*
FROM _tableDates AS d
    CROSS JOIN (
        SELECT 
            CASE 
                WHEN SUM(CASE 
                    WHEN MONTH(_Dates) = MONTH(CURRENT_DATE) 
                        AND YEAR(_Dates) = YEAR(CURRENT_DATE) 
                    THEN 1
                    ELSE 0 END) > 0 
                THEN CURRENT_DATE 
                ELSE CURRENT_DATE - INTERVAL 1 MONTH 
            END AS dt 
        FROM _tableDates) AS x
WHERE MONTH(d._Dates) = MONTH(x.dt) 
    AND YEAR(d._Dates) = YEAR(x.dt)

[transio]

By the way, doesn't work with your data above, because that's 2010... I updated to 2011 and it works fine.

[Miguel61]

Thanks Sir, thanks a lot.