SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Data for the month of December 2012

    happy new year 2013!

    the year has changed and there has been a problem in a query.

    I can't not retrieve the data for the month of December 2012, the output of alias contacttotals_previous_month is null.

    the problem is this case in the query:
    Code:
    	SUM(
    		CASE
    		WHEN MONTH (visitdate) = MONTH (
    			CURRENT_DATE () - INTERVAL 1 MONTH
    		)
    		AND YEAR (visitdate) = YEAR (CURRENT_DATE()) THEN
    			visitorcount
    		END
    	) AS contacttotals_previous_month,
    In the case January current year versus December previous year which solution can be find?
    thank you
    Code:
    mysql> SELECT
    	SUM(
    		CASE
    		WHEN visitorcount THEN
    			visitorcount
    		END
    	) AS contacttotals,
    	SUM(
    		CASE
    		WHEN MONTH (visitdate) = MONTH (CURRENT_DATE())
    		AND YEAR (visitdate) = YEAR (CURRENT_DATE()) THEN
    			visitorcount
    		END
    	) AS contacttotals_current_month,
    	SUM(
    		CASE
    		WHEN MONTH (visitdate) = MONTH (
    			CURRENT_DATE () - INTERVAL 1 MONTH
    		)
    		AND YEAR (visitdate) = YEAR (CURRENT_DATE()) THEN
    			visitorcount
    		END
    	) AS contacttotals_previous_month,
    	SUM(
    		CASE
    		WHEN visitdate = CURRENT_DATE () THEN
    			visitorcount
    		END
    	) AS contacttotals_today,
    	SUM(
    		CASE
    		WHEN visitdate = CURRENT_DATE () - INTERVAL 1 DAY THEN
    			visitorcount
    		END
    	) AS contacttotals_yest
    FROM
    	tbl_session;
    
    +---------------+-----------------------------+-------------------------------+--------------------+--------------------+
    | contacttotals | contacttotals_current_month | contacttotals_previous_month  | contacttotalsToday | contacttotals_yest |
    +---------------+-----------------------------+-------------------------------+--------------------+--------------------+
    | 8319824       | 1581                        | NULL                          | 1581               | 4433               |
    +---------------+-----------------------------+-------------------------------+--------------------+--------------------+
    
    1 row in set

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    stop using MONTH() and YEAR() functions, and use date ranges instead

    current month --
    Code:
    WHEN visitdate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
     AND visitdate  < LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY
    previous month --
    Code:
    WHEN visitdate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                                   - INTERVAL 1 MONTH
     AND visitdate  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    stop using MONTH() and YEAR() functions, and use date ranges instead

    current month --
    Code:
    WHEN visitdate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
     AND visitdate  < LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY
    previous month --
    Code:
    WHEN visitdate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                                   - INTERVAL 1 MONTH
     AND visitdate  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)
    thank you, but I have error:
    Code:
    [SQL] SELECT
    	SUM(
    		CASE
    		WHEN visitdate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY - INTERVAL 1 MONTH
    		AND visitdate < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)
    	) AS contacttotals_previous_month
    FROM tbl_session;
    [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 ') AS contacttotals_previous_month
    FROM tbl_sessioni' at line 6

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, you have error...

    you messed up the CASE syntax by forgetting some important parts of it
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I'm confused...

    Tried this:
    Code:
    [SQL] SELECT
    	SUM(
    		CASE
    		WHEN visitdate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY - INTERVAL 1 MONTH
    		AND visitdate < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) THEN
    			visitorcount
    		END
    	) AS contacttotals_previous_month
    FROM
    	tbl_session;
    [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
    			visitorcount
    		END
    	) AS contacttotals_previous_month
    FROM
    	tbl_ses' at line 5

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i also make mistakes

    previous month (corrected portion in red) --
    Code:
    WHEN visitdate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                                   - INTERVAL 1 MONTH
     AND visitdate  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thanks a lot!
    Goodbye


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
  •