This MySQL function returns the number of the quarter (1-4) for the date provided. The first quarter (i.e., three months) of each year has a value of 1.
Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).
This function returns the number of the quarter (1-4) for the date provided. The first quarter (i.e., three months) of each year has a value of 1.
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're using an IF statement to test whether reducing the quarter by one would yield a 0 value. If so, we'll set the user variable for the last quarter to 4. In the second statement we're establishing the year for the last quarter, based on the value determined for @LASTQTR. The last SQL statement selects rows and counts them where the QUARTER( ) function yields a value equal to the @LASTQTR variable and where the YEAR( ) function yields a value equal to the @YR variable based on the appointment date, and where the client is the one for which we are running the statement.