Hey everyone, I’m working on a project with a MySQL database that has an employee contracts table. I need to figure out how to group continuous date ranges for each employee. Here’s what I’ve got so far:
CREATE TABLE employee_contracts (
contract_id INT PRIMARY KEY AUTO_INCREMENT,
worker_id INT,
begin_date DATE,
finish_date DATE
);
INSERT INTO employee_contracts (worker_id, begin_date, finish_date) VALUES
(101, '2020-03-15', '2021-09-30'),
(101, '2021-10-01', '2022-06-30'),
(202, '2019-01-01', '2020-12-31'),
(303, '2021-04-01', '2021-12-31'),
(303, '2022-01-01', '2022-09-30'),
(303, '2023-02-01', '2023-08-31');
I want to combine the continuous date ranges for each worker, but keep separate entries if there’s a gap between contracts. Any tips on how to do this efficiently in MySQL? Thanks!
I’ve dealt with a similar issue in a project tracking employee tenure. Here’s an approach that worked well for me:
You can use a combination of self-joins and window functions to group continuous date ranges. The idea is to identify gaps between contracts and use that to create groups.
First, use LAG() to get the previous contract’s end date for each employee. Then, compare it with the current contract’s start date to find gaps. Finally, use a cumulative sum to create groups of continuous contracts.
Here’s a query that should do the trick:
WITH ranked_contracts AS (
SELECT *,
LAG(finish_date) OVER (PARTITION BY worker_id ORDER BY begin_date) AS prev_finish,
SUM(CASE WHEN begin_date <= LAG(finish_date) OVER (PARTITION BY worker_id ORDER BY begin_date) THEN 0 ELSE 1 END)
OVER (PARTITION BY worker_id ORDER BY begin_date) AS grp
FROM employee_contracts
)
SELECT worker_id, MIN(begin_date) AS start_date, MAX(finish_date) AS end_date
FROM ranked_contracts
GROUP BY worker_id, grp
ORDER BY worker_id, start_date;
This query should give you the grouped continuous date ranges for each employee. It’s been quite reliable in my experience, but you might need to tweak it slightly depending on your specific requirements.