sql-server - 计算给定时间段内预约请求的数量和可用医生的数量

Question

我有 3 张桌子，上面有时间段、医生预定的时间表和病人的预约请求。我想写一个查询来查找每个时间段可用的医生数量以及每个时间段的预约请求数量。

老虎机（10 分钟间隔）

Slot_id	插槽
1	2021-01-01 09:00
2	2021-01-01 09:10
3	2021-01-01 09:20

booking_gp_slots（为医生预订的时间段）

gp_id	预订时隙开始	预订时隙结束
10	2021-01-01 09:00	2021-01-01 10:00
10	2021-01-01 12:00	2021-01-01 12:20
24	2021-01-01 09:00	2021-01-01 09:40

request_slots（患者的预订请求）

患者 ID	req_slot_start	req_slot_end
1	2021-01-01 09:00	2021-01-01 09:30
3	2021-01-01 10:00	2021-01-01 10:30
5	2021-01-01 09:00	2021-01-01 09:40

样本输出

Slot_id	插槽	全科医生可用	患者请求
1	2021-01-01 09:00	2	5
2	2021-01-01 09:10	1	6
3	2021-01-01 09:20	3	6

由于没有 id 来连接表格，我发现很难计算可用医生和预约的数量。我的逻辑是检查医生预订的时段开始时间是否 > 时段时间，如果是这种情况，请将它们加起来。我无法汇总来自 CTE 的列。

任何建议或帮助将不胜感激。

with gp_slot as(SELECT 
gp_id, booked_slot_start, booked_slot_end
FROM 
booked_gp_slots
)

Select 
SUM(CASE WHEN
    (SELECT gp_slot.booked_slot_start FROM gp_slot) > S.Slots THEN 1 ELSE 0 END) AS GP_availibily
FROM Slots 

标签： sql-serverdatescheduleappointmenttimeslots