MySQL in a Nutshell, 2nd Edition

Name

QUARTER()

Synopsis

QUARTER(date)

This function returns the number of the quarter (1–4) for the date provided. The first quarter (i.e., the first three months) of each year has a value of 1. Here is an example:

SELECT COUNT(appointment)
AS 'Appts. Last Quarter'
FROM appointments
WHERE QUARTER(appointment) = (QUARTER(NOW( )) - 1)
AND client_id = '7393';

+---------------------+
| Appts. Last Quarter |
+---------------------+
|                  16 |
+---------------------+

In this example, MySQL calculates the total number of appointments for a particular client that occurred before the current quarter. The flaw in this SQL statement is that it doesn’t work when it’s run during the first quarter of a year. In the first quarter, the calculation on the fourth line would produce a quarter value of 0. This statement also doesn’t consider appointments in previous quarters of previous years. To solve these problems, we could set up user-defined variables for the values of the previous quarter and for its year:

SET @LASTQTR:=IF((QUARTER(CURDATE( ))-1) = 0, 4, QUARTER(CURDATE( ))-1);

SET @YR:=IF(@LASTQTR = 4, YEAR(NOW( ))-1, YEAR(NOW( )));

SELECT COUNT(appointment) AS 'Appts. Last Quarter'
FROM appointments
WHERE QUARTER(appointment) = @LASTQTR
AND YEAR(appointment) = @YR
AND client_id = '7393';

In the first SQL statement here, we use an IF statement to test whether reducing the quarter by 1 would yield a 0 value. If so, we’ll set the user variable for the last quarter to 4. In the second statement, we establish ...