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