如何计算诊所开放的小时数并考虑休息时间
如何计算诊所开放的小时数并考虑休息时间
提问于 2019-06-06 04:16:48
我使用的是MariaDB 10.3。
我正在尝试从提供者时间表中推导出诊所在给定工作日实际开放的小时数。
如果(1)所有的提供者都在吃午饭，或者(2)没有提供者在那个时候安排工作，那么诊所就被认为是“关闭的”。
如果至少有一个提供者还在工作，而其他提供者都在吃午饭，那么诊所就被认为是“开放的”。
考虑一下这个诊所，它有四个提供者，时间表是从星期一到星期三。
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'小提琴：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回答 3
Stack Overflow用户
回答已采纳
发布于 2019-06-06 06:47:43
请考虑以下几点：
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');然后，简而言之..。
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 |
+-----+-----------------+Stack Overflow用户
发布于 2019-06-06 04:42:29
扩展你的选择
SELECT 'Monday' DayOfTheWeek, MIN(mon_beg) clinicopen, MAX(mon_end) clinicclose, , MIN(mon_l_beg) pausestart, MAX(mon_1_end) pausenend FROM `schedule`一周的每一天，然后
SELECT DayofTheWeek, ((TIME_TO_SEC(SUBTIME(clinicclose, clinicopen)) - (TIME_TO_SEC(SUBTIME(IFNULL(pausenend,NOW()) , IFNULL(clinicopen,NOW()))) / (60 * 60)) HoursWorked所以你用诊所开放的第二秒减去暂停的秒数。如果paisestart为null，则取实际时间
Stack Overflow用户
发布于 2019-06-06 04:44:23
对于一周中的每一天，取午餐结束时间的最小值并减去午餐开始时间的最大值:如果结果小于或等于0，则诊所在午餐期间“开放”；否则，结果是诊所在午餐期间“关闭”的时间，应该从已经计算的HoursWorked中减去该小时数。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56467399
复制相关文章
相似问题
腾讯云开发者
