How to calculate hours that a clinic is open taking into account break times

I am using MariaDB 10.3.

I am attempting to derive how many hours a clinic is actually open for a given weekday from an provider schedule table.

The clinic is considered “closed” if (1) all providers are at lunch or (2) no providers are scheduled to work at that time.

If at least one provider is still working while the others are at lunch, the clinic is considered “open”.

Consider this clinic with four providers and a schedule from Monday to Wednesday.

CREATE TABLE `schedule` (
  `provider_id` char(10) NOT NULL,
  `mon_beg` time DEFAULT NULL,
  `mon_end` time DEFAULT NULL,
  `mon_l_beg` time DEFAULT NULL,
  `mon_l_end` time DEFAULT NULL,
  `tue_beg` time DEFAULT NULL,
  `tue_end` time DEFAULT NULL,
  `tue_l_beg` time DEFAULT NULL,
  `tue_l_end` time DEFAULT NULL,
  `wed_beg` time DEFAULT NULL,
  `wed_end` time DEFAULT NULL,
  `wed_l_beg` time DEFAULT NULL,
  `wed_l_end` time DEFAULT NULL,
  PRIMARY KEY (`provider_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `schedule` VALUES ('Alex','08:00:00','17:00:00','12:00:00','13:00:00','12:00:00','17:00:00',NULL,NULL,'07:00:00','18:00:00','11:00:00','12:00:00'),('Bob','08:00:00','17:00:00','12:00:00','13:00:00','08:00:00','17:00:00','12:00:00','13:00:00','08:00:00','17:00:00'

Fiddle: https://www.db-fiddle.com/f/GvkvRKwaZ3Eeusj77CA4J/0

SELECT DayofTheWeek, (TIME_TO_SEC(SUBTIME(clinicclose, clinicopen)) / (60 * 60)) HoursWorked
FROM (
SELECT 'Monday' DayOfTheWeek, MIN(mon_beg) clinicopen, MAX(mon_end) clinicclose FROM `schedule`
UNION ALL
SELECT 'Tuesday' DayOfTheWeek, MIN(tue_beg) clinicopen, MAX(tue_end) clinicclose FROM `schedule`
UNION ALL
SELECT 'Wednesday' DayOfTheWeek, MIN(wed_beg) clinicopen, MAX(wed_end) clinicclose FROM `schedule`
  ) calc


Expected Output:

DayOfTheWeek    HoursOpen
--------        ----------
Monday          8.5 (all providers take the same lunch)
Tuesday         9.5 (Alex is not scheduled for lunch, so the clinic is open)
Wednesday       11  (Bob and Charlie are still working while Alex is scheduled for lunch)
                    (Don should not affect the clinic's hours at all since he never comes in)

Actual Output:

Monday  9.5000
Tuesday 9.5000
Wednesday   11.0000

Answer

Consider the following…

DROP TABLE IF EXISTS schedule;

CREATE TABLE schedule
(provider_id INT NOT NULL
,day CHAR(3) NOT NULL 
,day_start TIME NOT NULL
,day_end TIME NOT NULL
,lunch_start TIME NULL
,lunch_end TIME NULL
,PRIMARY KEY(provider_id,day)
);

INSERT INTO schedule VALUES 
(1,'Mon','08:00:00','17:00:00','12:00:00','13:00:00'),
(1,'Tue','12:00:00','17:00:00',NULL,NULL),
(1,'Wed','07:00:00','18:00:00','11:00:00','12:00:00'),
(2,'Mon','08:00:00','17:00:00','12:00:00','13:00:00'),
(2,'Tue','08:00:00','17:00:00','12:00:00','13:00:00'),
(2,'Wed','08:00:00','17:00:00','12:00:00','13:00:00'),
(3,'Mon','08:00:00','17:00:00','12:00:00','13:00:00'),
(3,'Tue','08:00:00','17:00:00','12:00:00','13:00:00'),
(3,'Wed','08:00:00','17:00:00','12:00:00','13:00:00'),
(4,'Mon','07:30:00','16:30:00','12:00:00','13:00:00'),
(4,'Tue','07:30:00','17:00:00','12:00:00','13:00:00');

Then, crudely…

SELECT day
     , TIMEDIFF(TIMEDIFF(MAX(day_end),MIN(day_start))
              , TIMEDIFF(MIN(COALESCE(lunch_end,'12:00:00')),MAX(COALESCE(lunch_start,'12:00:00')))) delta
  FROM schedule 
 GROUP 
    BY day;

+-----+-----------------+
| day | delta           |
+-----+-----------------+
| Mon | 08:30:00.000000 |
| Tue | 09:30:00.000000 |
| Wed | 11:00:00.000000 |
+-----+-----------------+