Data for the month of December 2012

[cms9651]

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:

	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

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

[r937]

stop using MONTH() and YEAR() functions, and use date ranges instead

current month --

WHEN visitdate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
 AND visitdate  < LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY

previous month --

WHEN visitdate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                               - INTERVAL 1 MONTH
 AND visitdate  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE)

[cms9651]

stop using MONTH() and YEAR() functions, and use date ranges instead

current month --

WHEN visitdate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
 AND visitdate  < LAST_DAY(CURRENT_DATE) + INTERVAL 1 DAY

previous month --

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:

[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

[r937]

yes, you have error...

you messed up the CASE syntax by forgetting some important parts of it

[cms9651]

I'm confused...

Tried this:

[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

[r937]

i also make mistakes

previous month (corrected portion in red) --

WHEN visitdate >= CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY
                               - INTERVAL 1 MONTH
 AND visitdate  < CURRENT_DATE - INTERVAL DAYOFMONTH(CURRENT_DATE) - 1 DAY

[cms9651]

thanks a lot!
Goodbye