基于岛屿与间隙法的员工排班可用时段查询（时段长度不一致失效）

需为门店电话客服构建排班机制，确保任何时刻至少有一名员工可在岗接待客户。现有存储开放时段的表tbl_timeslots，包含staffid、from_date、to_date、isopen字段。本人编写的SQL在所有时段长度一致时可正常运行，但因需确保目标时段存在其他员工的连续时段“岛屿”（而非单个时段），当前SQL在时段长度不同时失效，寻求解决方案。

现有SQL语句 #

WITH all_slots AS 
(
    SELECT 
        slotid,
        from_date,
        to_date,
        staffid
    FROM 
        tbl_timeslots
    WHERE 
        isopen = 1
),
slot_boundaries AS 
(
    SELECT from_date AS date, staffid, 1 AS is_start
    FROM all_slots
    UNION ALL
    SELECT to_date AS date, staffid, -1 AS is_start
    FROM all_slots
),
island_formation AS 
(
    SELECT 
        date,
        staffid,
        SUM(is_start) OVER (PARTITION BY staffid ORDER BY date) AS open_count,
        SUM(is_start) OVER (PARTITION BY staffid ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS prev_open_count
    FROM 
        slot_boundaries
),
islands AS 
(
    SELECT
        staffid,
        date AS start_date,
        LEAD(date) OVER (PARTITION BY staffid ORDER BY date) AS end_date
    FROM 
        island_formation
    WHERE 
        prev_open_count = 0 AND open_count > 0
),
covered_slots AS 
(
    SELECT 
        x.slotid AS x_slotid,
        x.from_date AS x_from,
        x.to_date AS x_to,
        x.staffid AS x_staffid,
        i.staffid AS island_staffid,
        i.start_date AS island_start,
        i.end_date AS island_end
    FROM 
        all_slots x
    JOIN 
        islands i ON x.staffid != i.staffid 
                  AND i.start_date <= x.from_date 
                  AND (i.end_date >= x.to_date OR i.end_date IS NULL)
),
final_results AS 
(
    SELECT DISTINCT
        x_slotid,
        x_from,
        x_to,
        x_staffid,
        MIN(island_start) OVER (PARTITION BY x_slotid) AS island_start,
        MAX(island_end) OVER (PARTITION BY x_slotid) AS island_end
    FROM 
        covered_slots
)
SELECT * 
FROM final_results
WHERE island_start <= x_from 
  AND (island_end >= x_to OR island_end IS NULL)
ORDER BY x_from, x_to;

示例表结构及数据 #

CREATE TABLE [dbo].[tbl_timeslots](
    [slotid] [int] NULL,
    [staffid] [int] NULL,
    [from_date] [datetime] NULL,
    [to_date] [datetime] NULL,
    [isopen] [bit] NULL
) ON [PRIMARY]

INSERT [dbo].[tbl_timeslots] ([slotid], [staffid], [from_date], [to_date], [isopen]) VALUES (1, 1, CAST(N'2024-01-01T10:00:00.000' AS DateTime), CAST(N'2024-01-01T11:00:00.000' AS DateTime), 1)
INSERT [dbo].[tbl_timeslots] ([slotid], [staffid], [from_date], [to_date], [isopen]) VALUES (2, 1, CAST(N'2024-01-01T11:00:00.000' AS DateTime), CAST(N'2024-01-01T12:00:00.000' AS DateTime), 1)
INSERT [dbo].[tbl_timeslots] ([slotid], [staffid], [from_date], [to_date], [isopen]) VALUES (3, 1, CAST(N'2024-01-01T12:00:00.000' AS DateTime), CAST(N'2024-01-01T14:00:00.000' AS DateTime), 1)
INSERT [dbo].[tbl_timeslots] ([slotid], [staffid], [from_date], [to_date], [isopen]) VALUES (5, 1, CAST(N'2024-01-01T14:00:00.000' AS DateTime), CAST(N'2024-01-01T15:00:00.000' AS DateTime), 1)
INSERT [dbo].[tbl_timeslots] ([slotid], [staffid], [from_date], [to_date], [isopen]) VALUES (6, 2, CAST(N'2024-01-01T11:00:00.000' AS DateTime), CAST(N'2024-01-01T12:00:00.000' AS DateTime), 1)
INSERT [dbo].[tbl_timeslots] ([slotid], [staffid], [from_date], [to_date], [isopen]) VALUES (7, 2, CAST(N'2024-01-01T12:00:00.000' AS DateTime), CAST(N'2024-01-01T13:00:00.000' AS DateTime), 1)
INSERT [dbo].[tbl_timeslots] ([slotid], [staffid], [from_date], [to_date], [isopen]) VALUES (8, 2, CAST(N'2024-01-01T13:00:00.000' AS DateTime), CAST(N'2024-01-01T14:00:00.000' AS DateTime), 1)
INSERT [dbo].[tbl_timeslots] ([slotid], [staffid], [from_date], [to_date], [isopen]) VALUES (9, 2, CAST(N'2024-01-01T14:00:00.000' AS DateTime), CAST(N'2024-01-01T15:00:00.000' AS DateTime), 1)
INSERT [dbo].[tbl_timeslots] ([slotid], [staffid], [from_date], [to_date], [isopen]) VALUES (10, 3, CAST(N'2024-01-01T10:00:00.000' AS DateTime), CAST(N'2024-01-01T11:00:00.000' AS DateTime), 1)
INSERT [dbo].[tbl_timeslots] ([slotid], [staffid], [from_date], [to_date], [isopen]) VALUES (11, 3, CAST(N'2024-01-01T11:00:00.000' AS DateTime), CAST(N'2024-01-01T17:00:00.000' AS DateTime), 1)
INSERT [dbo].[tbl_timeslots] ([slotid], [staffid], [from_date], [to_date], [isopen]) VALUES (14, 4, CAST(N'2024-01-01T10:00:00.000' AS DateTime), CAST(N'2024-01-01T11:00:00.000' AS DateTime), 1)
INSERT [dbo].[tbl_timeslots] ([slotid], [staffid], [from_date], [to_date], [isopen]) VALUES (15, 4, CAST(N'2024-01-01T11:00:00.000' AS DateTime), CAST(N'2024-01-01T12:00:00.000' AS DateTime), 1)
INSERT [dbo].[tbl_timeslots] ([slotid], [staffid], [from_date], [to_date], [isopen]) VALUES (16, 4, CAST(N'2024-01-01T12:00:00.000' AS DateTime), CAST(N'2024-01-01T13:00:00.000' AS DateTime), 1)

内容的提问来源于stack exchange，提问作者user1480192