MySQL/MariaDB: 여러 개의 서로 다른 작업 시간 간격을 합산하여 사무실이 언제 열리는지 확인?

MySQL/MariaDB: 여러 개의 서로 다른 작업 시간 간격을 합산하여 사무실이 언제 열리는지 확인?

저는 공급자를 위한 유연한 스케줄링 옵션이 있는 환자 관리 시스템과 함께 일하고 있습니다.공급자가 언제든지 교대 근무를 시작하고 중단할 수 있고 공급자가 하루에 여러 교대 근무를 할 수 있다는 점을 고려하여 진료소(일명 사무실)가 문을 여는 시간을 추론해 보겠습니다.저는 MariaDB 10.3을 사용하고 있습니다.

이 이미지는 K 박사와 P 박사 두 공급자의 일정을 보여줍니다.

• K박사와 P박사는 아침 6시에 하루를 시작합니다.그들은 2시간 동안 일하다가 오전 8시에 2시간 동안 휴식을 취합니다.진료소는 2시간 동안 문을 열었습니다.

• K박사와 P박사는 10시에 다시 일을 시작합니다.그들은 점심을 먹기 위해 오후 12시에 다시 멈춥니다.진료소는 2시간 동안 문을 열었습니다.

• P박사는 오후 2시에 근무를 시작해서 오후 6시에 마칩니다.닥터 K는 오후 4시에 교대를 시작해서 오후 6시 30분에 끝납니다.진료소는 4.5시간동안 문을 열었습니다.

이 진료소의 영업 예정 시간은 총 8.5시간(2시간 + 2시간 + 4.5시간)입니다.

데이터베이스에 어떤 종류의 휴식시간이나 닫힌 시간에 대한 표시나 플래그가 없습니다.StartTime 및 StopTime 열에서 유추해야 합니다.

저의 초기 고려 사항은 재귀적 CTE를 사용하여 빈 시간 슬롯 목록을 작성한 다음 이 문제와 유사한 날의 최소 시작 시간 및 최대 정지 시간에서 해당 시간 슬롯의 합을 빼는 것이었습니다.하지만 이 병원은 모든 공급자가 일을 하지 않을 때만 문을 닫게 되어 일에 어려움을 겪게 됩니다.

관련 테이블 및 피들:

https://www.db-fiddle.com/f/pfNN1NJMAZB76EaPhinzsF/0

CREATE TABLE `scheduletest` (
    `ScheduleNum` BIGINT(20) NOT NULL DEFAULT 0,
    `SchedDate` DATE NULL,
    `StartTime` TIME NULL,
    `StopTime` TIME NULL,
    `ProvNum` BIGINT(20) NOT NULL
)
ENGINE=InnoDB
;

CREATE TABLE `providertest` (
    `ProvNum` BIGINT(20) NOT NULL DEFAULT 0,
    `Abbr` VARCHAR(255) NULL DEFAULT NULL
)
ENGINE=InnoDB
;

INSERT INTO `providertest` (`ProvNum`,`Abbr`) VALUES 
(1,'DR K')
,(2,'DR P')
;

INSERT INTO `scheduletest` (`ScheduleNum`,`SchedDate`,`StartTime`,`StopTime`,`ProvNum`) VALUES 
(79786,'2019-06-06','06:00:00','08:00:00',1)
,(79787,'2019-06-06','10:00:00','12:00:00',1)
,(79788,'2019-06-06','06:00:00','08:00:00',2)
,(79789,'2019-06-06','10:00:00','12:00:00',2)
,(79790,'2019-06-06','14:00:00','18:00:00',2)
,(79791,'2019-06-06','16:00:00','18:30:00',1)
;

언급URL : https://stackoverflow.com/questions/56487952/mysql-mariadb-summing-multiple-disparate-worktime-invertals-to-determine-when-a