Problem with query MONTH(`_Dates`)

Databases
1

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
2

Something like this?

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

3

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?

4

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…)

5

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
6

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)
7

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

8

Thanks Sir, thanks a lot. :slight_smile: