Counting the number of calendar events in each month of the year

Formulation of the problem:
display the number of events for each month of the year.

Each event has two fields.
- start_date - start date of event
- end_date - the end date of the event

The structure of the table with calendar events:

CREATE TABLE `events` (
`id` int (11) unsigned NOT NULL auto_increment,
`start_date` date default NULL ,
`end_date` date default NULL ,
`created` datetime default NULL ,
`modified` datetime default NULL ,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

* This source code was highlighted with Source Code Highlighter .

')
Expected Result:

January	21	February	34	March	47
May	eight	June	12	July	23
August	56	September	eleven	October	35
November	34	Desember	24

Possible options:

- MONTH (start_date) = MONTH (end_date), YEAR (start_date) = YEAR (end_date);
The event begins and ends in the same month of one year.

- MONTH (start_date) <MONTH (end_date), YEAR (start_date) = YEAR (end_date);
The event begins in one month, and ends in another month of one year.

- MONTH (start_date) = MONTH (end_date), YEAR (start_date) <YEAR (end_date);
The event begins and ends in the same month but in different years (it just lasts a year).

- MONTH (start_date) <MONTH (end_date), YEAR (start_date) <YEAR (end_date);
The event begins and ends in different months of different years (started in December, ended in January).

Decision:
This problem can be solved in two ways: display values ​​in a row (each column is a month), or in a column (each line is a month with a number of events).
Having worn out with JOINs, it was decided to output the results into a string (the unlimited duration of the event became problematic in using the JOIN). That is, they decided to solve the problem in the "forehead", limit the events by year and check in which months the event takes place. To check the occurrence was written MYSQL function.

Implementation:

DELIMITER $$

DROP FUNCTION IF EXISTS `isEventInMonth`$$

CREATE FUNCTION `isEventInMonth`(
startdate DATE ,
enddate DATE ,
_month INT (1),
_year INT (1)
) RETURNS INT (1)
BEGIN
DECLARE results INT (1);

IF YEAR (startdate) = YEAR (enddate) THEN
SET results = IF ( MONTH (startdate) = _month
AND MONTH (enddate) = _month
OR
( MONTH (startdate) <> MONTH (enddate)
AND ( MONTH (startdate) <= _month
AND MONTH (enddate) >= _month)
)
,1,0);
ELSEIF YEAR (startdate) = _year THEN
SET results = IF ( MONTH (startdate) <= _month ,1,0);
ELSE
SET results = IF ( MONTH (enddate) >= _month ,1,0);
END IF ;

RETURN results;
END $$

DELIMITER ;

* This source code was highlighted with Source Code Highlighter .

This input function receives the start date of the event, the end date of the event, month and year for which it is necessary to perform a check, and returns 0 or 1. As a result, the function checks the occurrence of the current month in the start and end intervals of the event.

Using:

SELECT
SUM (isEventInMonth(start_date,end_date,1,2011)) AS jan,
SUM (isEventInMonth(start_date,end_date,2,2011)) AS feb,
SUM (isEventInMonth(start_date,end_date,3,2011)) AS mar,
SUM (isEventInMonth(start_date,end_date,4,2011)) AS apr,
SUM (isEventInMonth(start_date,end_date,5,2011)) AS may,
SUM (isEventInMonth(start_date,end_date,6,2011)) AS jun,
SUM (isEventInMonth(start_date,end_date,7,2011)) AS jul,
SUM (isEventInMonth(start_date,end_date,8,2011)) AS aug,
SUM (isEventInMonth(start_date,end_date,9,2011)) AS sep,
SUM (isEventInMonth(start_date,end_date,10,2011)) AS 'oct' ,
SUM (isEventInMonth(start_date,end_date,11,2011)) AS nov,
SUM (isEventInMonth(start_date,end_date,12,2011)) AS 'dec'
FROM EVENTS WHERE YEAR (start_date) = 2011 OR YEAR (end_date) = 2011

* This source code was highlighted with Source Code Highlighter .

The result will be in a row, in each column the number of events per month of a particular year.

Summarizing:
The implementation does not pretend to elegance, I think that someone will be useful in the implementation of such standard functionality as a calendar of events.
Questions and improvements are welcome.
Ps. Do not look for the title and description fields in the nameplate - these are translatable fields and are not in this table.

UPD:

User xtender offered a more correct solution to the problem.

SELECT <br>
sum ( CASE when t.`start_date`< '2010-02-01' and t.end_date>= '2010-01-01' then 1 else 0 end ) AS jan,<br>
sum ( CASE when t.`start_date`< '2010-03-01' and t.end_date>= '2010-02-01' then 1 else 0 end ) AS feb,<br>
sum ( CASE when t.`start_date`< '2010-04-01' and t.end_date>= '2010-03-01' then 1 else 0 end ) AS mar,<br>
sum ( CASE when t.`start_date`< '2010-05-01' and t.end_date>= '2010-04-01' then 1 else 0 end ) AS apr,<br>
sum ( CASE when t.`start_date`< '2010-06-01' and t.end_date>= '2010-05-01' then 1 else 0 end ) AS may,<br>
sum ( CASE when t.`start_date`< '2010-07-01' and t.end_date>= '2010-06-01' then 1 else 0 end ) AS jun,<br>
sum ( CASE when t.`start_date`< '2010-08-01' and t.end_date>= '2010-07-01' then 1 else 0 end ) AS jul,<br>
sum ( CASE when t.`start_date`< '2010-09-01' and t.end_date>= '2010-08-01' then 1 else 0 end ) AS aug,<br>
sum ( CASE when t.`start_date`< '2010-10-01' and t.end_date>= '2010-09-01' then 1 else 0 end ) AS sep,<br>
sum ( CASE when t.`start_date`< '2010-11-01' and t.end_date>= '2010-10-01' then 1 else 0 end ) AS oct,<br>
sum ( CASE when t.`start_date`< '2010-12-01' and t.end_date>= '2010-11-01' then 1 else 0 end ) AS nov,<br>
sum ( CASE when t.`start_date`< '2011-01-01' and t.end_date>= '2010-12-01' then 1 else 0 end ) AS dec <br>
FROM events t <br>
<br>
* This source code was highlighted with Source Code Highlighter .