SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Problem with query MONTH(`_Dates`)

    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?

    PHP Code:
    SELECT `_Dates
       
    FROM _tableDates
          WHERE
             MONTH
    (`_Dates`) = MONTH(`_Dates`())
             OR 
    MONTH(`_Dates`) = MONTH(`_Dates`)-

  2. #2
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Something like this?

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

  3. #3
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by transio View Post
    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
    Code:
    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
    Code:
    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. #4
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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):
    Code:
    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. #5
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thanks Sir, but I have this error in your query:

    PHP Code:
    [SQLSELECT 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) > 
                    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

    [Err1064 You have an error in your SQL syntaxcheck 
    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. #6
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fixed:
    Code:
    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. #7
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By the way, doesn't work with your data above, because that's 2010... I updated to 2011 and it works fine.

  8. #8
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    402
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thanks Sir, thanks a lot.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •