Sherlock Sherlock - 29 days ago 16
MySQL Question

Count the number of records per month

I have records with a start and end date, like so:

id start_date end_date
1 2016-01-01 2016-10-31
2 2016-06-01 2016-12-31
3 2016-06-01 2016-07-31


I have to know the number of records that were active per month (or better put: on the first day of all months in a given period). The counts would look like this when calculated for 2016:

jan: 1
feb: 1
mar: 1
apr: 1
may: 1
jun: 3
jul: 3
aug: 2
sep: 2
oct: 2
nov: 1
dec: 1


The solution I came up with, is to create a TEMP TABLE with all applicable dates for the given period:

date
2016-01-01
2016-02-01
...


Which makes the query very easy:

SELECT
COUNT(*),
m.date
FROM
months m
INNER JOIN table t
ON m.date BETWEEN t.start_date AND t.end_date
GROUP BY
m.date


This produces exactly the results I'm looking for. However; I do feel as if this could be done easier. I just don't know how.

Any suggestions?

Answer

As suggested in the comments, I replaced the temp table with a permanent table called 'calendar'.

CREATE TABLE `calendar` (
  `date` date NOT NULL,
  PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I filled this table with all dates from 2000-01-01 until 2100-12-31. I rewrote my query to this:

SELECT
  COUNT(*),
  c.date
FROM
  calendar c
INNER JOIN table t
  ON c.date BETWEEN t.start_date AND t.end_date
WHERE
  DAYOFMONTH(c.date) = 1
AND
  c.date BETWEEN '2016-01-01' AND '2016-12-31'
GROUP BY
  c.date