Calculate average for each month for a given date range

Question

I have the employees table where each employee has a related start_date, end_date and a salary.

NOTE: on the bottom you can find the SQL code to import the structure and data.

+----+-------+------------+------------+---------+
| id | name  | start_date | end_date   | salary  |
+----+-------+------------+------------+---------+
|  1 | Mark  | 2017-05-01 | 2020-01-31 | 2000.00 |
|  2 | Tania | 2018-02-01 | 2019-08-31 | 5000.00 |
|  3 | Leo   | 2018-02-01 | 2018-09-30 | 3000.00 |
|  4 | Elsa  | 2018-12-01 | 2020-05-31 | 4000.00 |
+----+-------+------------+------------+---------+

The problem

For a given date range I want to extract the average of the salaries for each month within the given date range.

UPDATE: I would like to have the solution for MySQL 5.6 but it would be great to have also the solution for MySQL 8+ (just for personal knowledge).

Example

If the date range is 2018-08-01 - 2019-01-31, the SQL statement should loop from August 2018 to January 2019 and it has to calculate the average salary for each month:

• in August 2018 the active employees are Mark, Tania, Leo (because August 2018 is between their start_date and end_date) so the average is 3333.33
• in September 2018 the active employees are Mark, Tania, Leo (because September 2018 is between their start_date and end_date) so the average is 3333.33
• in October 2018 the active employees are Mark, Tania so the average is 3500.00
• in November 2018 the active employees are Mark, Tania so the average is 3500.00
• in December 2018 the active employees are Mark, Tania, Elsa so the average is 3666.6667
• in January 2019 the active employees are Mark, Tania, Elsa so the average is 3666.6667

Following you can see the expected result for the date range 2018-08-01 - 2019-01-31

+------+-------+------------+
| year | month | avg_salary |
+------+-------+------------+
| 2018 | 08    | 3333.33    |
| 2018 | 09    | 3333.33    |
| 2018 | 10    | 3500.00    |
| 2018 | 11    | 3500.00    |
| 2018 | 12    | 3666.67    |
| 2019 | 01    | 3666.67    |
+------+-------+------------+

NOTE: I solved this problem mixing MySQL with PHP code but for big date range it has to execute too many queries (one each month). So I would like to have a solution using MySQL only.

SQL to import structure and data

CREATE TABLE `employees` (
  `id` int(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `salary` decimal(10,2) DEFAULT NULL
);

INSERT INTO `employees` (`id`, `name`, `start_date`, `end_date`, `salary`) VALUES
(1, 'Mark', '2017-05-01', '2020-01-31', '2000.00'),
(2, 'Tania', '2018-02-01', '2019-08-31', '5000.00'),
(3, 'Leo', '2018-02-01', '2018-09-30', '3000.00'),
(4, 'Elsa', '2018-12-01', '2020-05-31', '4000.00');

Answer 1

Here's a MySQL 8.0 recursive CTE way of doing it. The CTE creates a list of all the year, month combinations between the minimum start_date and maximum end_date in the employees table, which is then LEFT JOINed to the employees table to get the average salary for all employees who were working in that particular year and month:

WITH RECURSIVE months (year, month) AS
(
  SELECT YEAR(MIN(start_date)) AS year, MONTH(MIN(start_date)) AS month FROM employees
  UNION ALL
  SELECT year + (month = 12), (month % 12) + 1 FROM months
  WHERE STR_TO_DATE(CONCAT_WS('-', year, month, '01'), '%Y-%m-%d') <= (SELECT MAX(end_date) FROM employees)
)
SELECT m.year, m.month, ROUND(AVG(e.salary), 2) AS avg_salary
FROM months m
LEFT JOIN employees e ON STR_TO_DATE(CONCAT_WS('-', m.year, m.month, '01'), '%Y-%m-%d') BETWEEN e.start_date AND e.end_date
WHERE STR_TO_DATE(CONCAT_WS('-', m.year, m.month, '01'), '%Y-%m-%d') BETWEEN '2018-08-01' AND '2019-01-31'
GROUP BY m.year, m.month

Output:

year    month   avg_salary
2018    8       3333.33
2018    9       3333.33
2018    10      3500.00
2018    11      3500.00
2018    12      3666.67
2019    1       3666.67

Demo on dbfiddle