MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE calendar (
  calendar_date    DATE,
  is_working_day   INT
);
INSERT INTO
  calendar
VALUES
                      ('2023-01-10', 1),  ('2023-01-20', 1),  ('2023-01-30', 1),
  ('2023-01-01', 0),  ('2023-01-11', 1),  ('2023-01-21', 0),  ('2023-01-31', 1),
  ('2023-01-02', 0),  ('2023-01-12', 1),  ('2023-01-22', 0),
  ('2023-01-03', 1),  ('2023-01-13', 1),  ('2023-01-23', 1),
  ('2023-01-04', 1),  ('2023-01-14', 0),  ('2023-01-24', 1),
  ('2023-01-05', 1),  ('2023-01-15', 0),  ('2023-01-25', 1),
  ('2023-01-06', 1),  ('2023-01-16', 1),  ('2023-01-26', 1),
  ('2023-01-07', 0),  ('2023-01-17', 1),  ('2023-01-27', 1),
  ('2023-01-08', 0),  ('2023-01-18', 1),  ('2023-01-28', 0),
  ('2023-01-09', 1),  ('2023-01-19', 1),  ('2023-01-29', 0)

-- 1st is a sunday, 2nd is a bank holiday

Records: 31  Duplicates: 0  Warnings: 0


CREATE TABLE schedule (
  employee	    VARCHAR(32),
  taskname	    VARCHAR(32),
  startDate	    DATE,
  endDate       DATE
)
INSERT INTO
  schedule
VALUES
  ('Mr. Anderson',	'Programming', '2023-01-02', '2023-01-06'),
  ('Mr. Anderson',	'Programming', '2023-01-09', '2023-01-14'),
  ('Mr. Anderson',	'Vacation',    '2023-01-14', '2023-01-31')

Records: 3  Duplicates: 0  Warnings: 0


CREATE TABLE activities (
  taskname                 VARCHAR(32),
  count_non_working_days   INT
)
INSERT INTO
  activities
VALUES
  ('Programming', 1),
  ('Vacation',    0)

Records: 2  Duplicates: 0  Warnings: 0


SELECT
  s.employee,
  s.taskname,
  s.startDate,
  s.endDate,
  c.calendar_date
FROM
(
    schedule    AS s
  INNER JOIN
    activities  AS a
      ON  a.taskname = s.taskname
)
INNER JOIN
  calendar    AS c
    ON  c.calendar_date  >= s.startDate
    AND c.calendar_date  <= s.endDate
    AND c.is_working_day >= 1 - a.count_non_working_days 
WHERE
      c.calendar_date >= '2023-01-01'
  AND c.calendar_date <= '2023-12-31'
ORDER BY
  s.employee,
  s.taskname,
  s.startDate,
  s.endDate,
  c.calendar_date


employee
taskname
startDate
endDate
calendar_date


Mr. Anderson
Programming
2023-01-02
2023-01-06
2023-01-02

Mr. Anderson
Programming
2023-01-02
2023-01-06
2023-01-03

Mr. Anderson
Programming
2023-01-02
2023-01-06
2023-01-04

Mr. Anderson
Programming
2023-01-02
2023-01-06
2023-01-05

Mr. Anderson
Programming
2023-01-02
2023-01-06
2023-01-06

Mr. Anderson
Programming
2023-01-09
2023-01-14
2023-01-09

Mr. Anderson
Programming
2023-01-09
2023-01-14
2023-01-10

Mr. Anderson
Programming
2023-01-09
2023-01-14
2023-01-11

Mr. Anderson
Programming
2023-01-09
2023-01-14
2023-01-12

Mr. Anderson
Programming
2023-01-09
2023-01-14
2023-01-13

Mr. Anderson
Programming
2023-01-09
2023-01-14
2023-01-14

Mr. Anderson
Vacation
2023-01-14
2023-01-31
2023-01-16

Mr. Anderson
Vacation
2023-01-14
2023-01-31
2023-01-17

Mr. Anderson
Vacation
2023-01-14
2023-01-31
2023-01-18

Mr. Anderson
Vacation
2023-01-14
2023-01-31
2023-01-19

Mr. Anderson
Vacation
2023-01-14
2023-01-31
2023-01-20

Mr. Anderson
Vacation
2023-01-14
2023-01-31
2023-01-23

Mr. Anderson
Vacation
2023-01-14
2023-01-31
2023-01-24

Mr. Anderson
Vacation
2023-01-14
2023-01-31
2023-01-25

Mr. Anderson
Vacation
2023-01-14
2023-01-31
2023-01-26

Mr. Anderson
Vacation
2023-01-14
2023-01-31
2023-01-27

Mr. Anderson
Vacation
2023-01-14
2023-01-31
2023-01-30

Mr. Anderson
Vacation
2023-01-14
2023-01-31
2023-01-31


SELECT
  s.employee,
  s.taskname,
  COUNT(*)    AS total_days
FROM
(
    schedule    AS s
  INNER JOIN
    activities  AS a
      ON  a.taskname = s.taskname
)
INNER JOIN
  calendar    AS c
    ON  c.calendar_date  >= s.startDate
    AND c.calendar_date  <= s.endDate
    AND c.is_working_day >= 1 - a.count_non_working_days 
WHERE
      c.calendar_date >= '2023-01-01'
  AND c.calendar_date <= '2023-12-31'
GROUP BY
  s.employee,
  s.taskname


employee
taskname
total_days


Mr. Anderson
Programming
11

Mr. Anderson
Vacation
12

employee	taskname	startDate	endDate	calendar_date
Mr. Anderson	Programming	2023-01-02	2023-01-06	2023-01-02
Mr. Anderson	Programming	2023-01-02	2023-01-06	2023-01-03
Mr. Anderson	Programming	2023-01-02	2023-01-06	2023-01-04
Mr. Anderson	Programming	2023-01-02	2023-01-06	2023-01-05
Mr. Anderson	Programming	2023-01-02	2023-01-06	2023-01-06
Mr. Anderson	Programming	2023-01-09	2023-01-14	2023-01-09
Mr. Anderson	Programming	2023-01-09	2023-01-14	2023-01-10
Mr. Anderson	Programming	2023-01-09	2023-01-14	2023-01-11
Mr. Anderson	Programming	2023-01-09	2023-01-14	2023-01-12
Mr. Anderson	Programming	2023-01-09	2023-01-14	2023-01-13
Mr. Anderson	Programming	2023-01-09	2023-01-14	2023-01-14
Mr. Anderson	Vacation	2023-01-14	2023-01-31	2023-01-16
Mr. Anderson	Vacation	2023-01-14	2023-01-31	2023-01-17
Mr. Anderson	Vacation	2023-01-14	2023-01-31	2023-01-18
Mr. Anderson	Vacation	2023-01-14	2023-01-31	2023-01-19
Mr. Anderson	Vacation	2023-01-14	2023-01-31	2023-01-20
Mr. Anderson	Vacation	2023-01-14	2023-01-31	2023-01-23
Mr. Anderson	Vacation	2023-01-14	2023-01-31	2023-01-24
Mr. Anderson	Vacation	2023-01-14	2023-01-31	2023-01-25
Mr. Anderson	Vacation	2023-01-14	2023-01-31	2023-01-26
Mr. Anderson	Vacation	2023-01-14	2023-01-31	2023-01-27
Mr. Anderson	Vacation	2023-01-14	2023-01-31	2023-01-30
Mr. Anderson	Vacation	2023-01-14	2023-01-31	2023-01-31