I have a vacation table and data. The table has an idemployee column, a vacation_from column, and a vacation_to column.

I need working periods (start and end) for example from January 1, 2023 to December 31, 2023. How do I do this Query?

vacation Table

CREATE TABLE `vacation` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `idemployee` INT NOT NULL,
  `vacation_from` DATE NULL,
  `vacation_to` DATE NULL,
  PRIMARY KEY (`id`));

insert Data

INSERT INTO `` (`id`,`idemployee`,`vacation_from`,`vacation_to`) VALUES (1,1,'2023-02-01','2023-02-07');
INSERT INTO `` (`id`,`idemployee`,`vacation_from`,`vacation_to`) VALUES (2,1,'2023-06-15',NULL);
INSERT INTO `` (`id`,`idemployee`,`vacation_from`,`vacation_to`) VALUES (3,2,'2022-12-25','2023-01-05');
INSERT INTO `` (`id`,`idemployee`,`vacation_from`,`vacation_to`) VALUES (4,3,'2023-03-10','2023-03-15');
INSERT INTO `` (`id`,`idemployee`,`vacation_from`,`vacation_to`) VALUES (5,4,'2023-12-20','2024-01-05');

 Table: vacation

I have a vacation table and data. The table has an idemployee column, a vacation_from column, and a vacation_to column.

I need working periods (start and end) for example from January 1, 2023 to December 31, 2023. How do I do this Query?

vacation Table

CREATE TABLE `vacation` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `idemployee` INT NOT NULL,
  `vacation_from` DATE NULL,
  `vacation_to` DATE NULL,
  PRIMARY KEY (`id`));

insert Data

INSERT INTO `` (`id`,`idemployee`,`vacation_from`,`vacation_to`) VALUES (1,1,'2023-02-01','2023-02-07');
INSERT INTO `` (`id`,`idemployee`,`vacation_from`,`vacation_to`) VALUES (2,1,'2023-06-15',NULL);
INSERT INTO `` (`id`,`idemployee`,`vacation_from`,`vacation_to`) VALUES (3,2,'2022-12-25','2023-01-05');
INSERT INTO `` (`id`,`idemployee`,`vacation_from`,`vacation_to`) VALUES (4,3,'2023-03-10','2023-03-15');
INSERT INTO `` (`id`,`idemployee`,`vacation_from`,`vacation_to`) VALUES (5,4,'2023-12-20','2024-01-05');

 Table: vacation

 I want this Output:

DB Link with my wrong query

• mysql

• created fiddle : db-fiddle/f/oHGzku4sFeY4E1tgTaK3zP/0 – Suresh Kamrushi Commented Mar 4 at 12:09
• Please provide more clarity and table involve. with this single table it seems not possible. – Suresh Kamrushi Commented Mar 4 at 12:10
• what do you mean with working periods? so you want to know from this table when Employee smith actually does work and isnt on vacation? – Bending Rodriguez Commented Mar 4 at 13:03
• I would start off first with inserting vacation tupples for holidays for each employee assuming they all work in the same country – Bending Rodriguez Commented Mar 4 at 13:06
• 1 There is no way of knowing from the published data when an employee began employment so if the first from is 2023-05-01 it isn't necessarily true that he/she was working from 2023-01-01 to 2023-04-30.. – P.Salmon Commented Mar 4 at 15:01

1 Answer 1

WITH 
employees (idemployee) AS (
  SELECT DISTINCT idemployee
  FROM vacation
  ),
date_points (idemployee, date_point, weight) AS (
  SELECT idemployee, @date_from date_point, 1 weight 
  FROM employees
  UNION ALL
  SELECT idemployee, @date_to, -1
  FROM employees
  UNION ALL
  SELECT idemployee, vacation_from, -1
  FROM vacation
  UNION ALL
  SELECT idemployee, vacation_to, 1
  FROM vacation
  WHERE vacation_to IS NOT NULL
  ),
date_weights (idemployee, date_point, point_weight) AS (
  SELECT idemployee, date_point, 
         SUM(weight) OVER (PARTITION BY idemployee ORDER BY date_point) 
  FROM date_points
  ),
date_groups AS (
  SELECT idemployee, date_point, 
         SUM(point_weight = 0) OVER (PARTITION BY idemployee ORDER BY date_point DESC) group_no
  FROM date_weights
  )
SELECT idemployee,
       MIN(date_point) + INTERVAL (MIN(date_point) <> @date_from) DAY work_start,
       MAX(date_point) - INTERVAL (MAX(date_point) <> @date_to) DAY work_end
FROM date_groups
WHERE group_no > 0
GROUP BY idemployee, group_no
HAVING work_start >= @date_from
   AND work_end <= @date_to
ORDER BY idemployee, work_start

step-by-step fiddle