我正试图从机构管理系统查询有关开放预约时间的数据，并具有只读访问权限。有一列表示可用于预订的阻塞时段，还有一列表示时间是否已预订的已预订时段。24小时内的每5分钟间隔表示为0或1。可用时间将由阻塞的时隙列中的1和预订的时隙列的0表示。迭代并返回可用日期时间的最佳方式是什么？
到目前为止，我已经尝试创建案例语句，如果小时可用，则返回时间作为标题，并返回值"OPEN"，但我只需要返回每个提供者和位置的每个日期/时间，前提是它可用。
这是当前返回日期、位置、部门、资源、阻止的插槽和预订的插槽的代码：
Select
Cast(vwApptSchedAvail.Available_Date As datetime) As [Date],
vwApptSchedAvail.Location As [Location],
vwApptSchedAvail.Department As [Department],
vwApptSchedAvail.Resource As [Resource],
Available_Days.Blocked_Slots1 + Available_Days.Blocked_Slots2 As [Blocked Slots],
Available_Days.Booked_Slots1 + Available_Days.Booked_Slots2 As [Booked Slots]
From
PM.vwApptSchedAvail vwApptSchedAvail Inner Join
PM.Available_Days Available_Days On vwApptSchedAvail.Available_Date =
Available_Days.Available_Date And vwApptSchedAvail.Resource_ID =
Available_Days.Resource_ID And vwApptSchedAvail.Scheduling_Department_ID =
Available_Days.Scheduling_Department_ID And
vwApptSchedAvail.Scheduling_Location_ID =
Available_Days.Scheduling_Location_ID
Where
vwApptSchedAvail.Available_Date Between '2019-10-23' And '2019-10-23' And
vwApptSchedAvail.Location = 'Location A' And
vwApptSchedAvail.Department = 'OP' And
vwApptSchedAvail.Booking_Factor > 0
Group By
Cast(vwApptSchedAvail.Available_Date As date),
vwApptSchedAvail.Location, vwApptSchedAvail.Department,
vwApptSchedAvail.Resource,
vwApptSchedAvail.Booking_Factor, Available_Days.Blocked_Slots1,
Available_Days.Blocked_Slots2, Available_Days.Booked_Slots1,
Available_Days.Booked_Slots2
我的查询返回如下内容：
+============+============+============+==============+==================================================================================================================================================================================================================================================================================================+==================================================================================================================================================================================================================================================================================================+
| Date | Location | Department | Resource | Blocked Slots | Booked Slots |
+============+============+============+==============+==================================================================================================================================================================================================================================================================================================+==================================================================================================================================================================================================================================================================================================+
| 10/23/2019 | Location A | OP | Provider 1 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
+------------+------------+------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10/23/2019 | Location A | OP | Provider 2 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111000000000000111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000 |
+------------+------------+------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10/23/2019 | Location A | OP | Provider 3 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111000000000000111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111000000000000000000000000111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
+------------+------------+------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10/23/2019 | Location A | OP | Provider 4 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 | 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111110000222222222222111111112222111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
+------------+------------+------------+--------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
这就是当我使用上面提到的案例语句时返回的数据：
+============+============+============+==============+=========+=========+=========+=========+=========+=========+=========+=========+=========+=========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+
| Date | Location | Department | Resource | 0:00:00 | 1:00:00 | 2:00:00 | 3:00:00 | 4:00:00 | 5:00:00 | 6:00:00 | 7:00:00 | 8:00:00 | 9:00:00 | 10:00:00 | 11:00:00 | 12:00:00 | 13:00:00 | 14:00:00 | 15:00:00 | 16:00:00 | 17:00:00 | 18:00:00 | 19:00:00 | 20:00:00 | 21:00:00 | 22:00:00 | 23:00:00 |
+============+============+============+==============+=========+=========+=========+=========+=========+=========+=========+=========+=========+=========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+==========+
| 10/23/2019 | Location A | OP | Provider 2 | | | | | | | | | | | OPEN | | | | | OPEN | | | | | | | | |
+------------+------------+------------+--------------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+----------+
这比0和1更好，但仍然不是我需要的格式。
我最终希望能够返回这样的东西：
+==================+============+============+==============+
| Date/Time | Location | Department | Resource |
+==================+============+============+==============+
| 10/23/19 5:00 PM | Location A | OP | Provider 1 |
+------------------+------------+------------+--------------+
| 10/23/19 6:00 PM | Location A | OP | Provider 1 |
+------------------+------------+------------+--------------+
| 10/23/19 7:00 PM | Location A | OP | Provider 1 |
+------------------+------------+------------+--------------+
| 10/23/19 4:00 PM | Location A | OP | Provider 2 |
+------------------+------------+------------+--------------+
| 10/23/19 5:00 PM | Location A | OP | Provider 2 |
+------------------+------------+------------+--------------+
| 10/23/19 7:00 PM | Location A | OP | Provider 2 |
+------------------+------------+------------+--------------+
感谢您将示例数据以文本形式发布，以便我们可以轻松地制作此耗材。
这个问题有两大障碍。第一个问题是将字符串的噩梦解析为可用的东西。解决这一问题最简单的方法是使用一个统计表。我在这里创建了一个cte。拥有一个持久的索引表应该会带来一些性能上的好处，但不是那么多。在这里使用一个计数表是我如何处理第一步。
declare @Something table
(
ApptDate datetime
, LocationA varchar(20)
, OP char(2)
, Provider varchar(20)
, BlockedSlots varchar(500)
, BookedSlots varchar(500)
)
insert @Something values
('10/23/2019'
, 'Location A'
, 'OP'
, 'Provider 1'
, '000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
, '000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
)
,('10/23/2019'
, 'Location A'
, 'OP'
, 'Provider 2'
, '000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000'
, '000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111000000000000111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000'
)
,('10/23/2019'
, 'Location A'
, 'OP'
, 'Provider 3'
, '000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111000000000000111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
, '000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111000000000000000000000000111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
)
,('10/23/2019'
, 'Location A'
, 'OP'
, 'Provider 4'
, '000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111111111111111111111111111111111111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
, '000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000111111110000222222222222111111112222111111111111000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
)
;
with E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
E2(N) AS (SELECT 1 FROM E1 a cross join E1 b cross join E1 c), --this is enough for 1,000 rows. Way more than we need here
cteTally(N) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E2
)
select s.ApptDate
, s.LocationA
, s.OP
, s.Provider
, TimeIsBlocked = substring(s.BlockedSlots, t.N, 1)
, BlockStartTime = dateadd(minute, 5 * (t.N - 1), ApptDate)
, BlockEndTime = dateadd(minute, (5 * (t.N - 1)) + 5, ApptDate)
, TimeIsBooked = substring(s.BookedSlots, t.N, 1)
from @Something s
join cteTally t on t.N <= 288 --the number of 5 minute time slots in a 24 hour period
order by s.Provider
, t.N
这将返回样本数据(您的主查询(的每行288行。每五分钟一个。它还将返回该时段的开始和结束时间，以及该时间是否被阻止和/或预订。从这里开始，您需要使用间隙和孤岛方法隔离有效的可用时隙。这个问题已经被问了成百上千次了。毫无疑问，讨论如何处理这个问题的最好的文章来自已故伟大的德怀恩·坎普斯。https://www.red-gate.com/simple-talk/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/
首先看一下这个解析逻辑，了解它在做什么。然后拍一张这张照片的缺口和岛屿。如果你需要的话，我可以帮助你完成最后的冲刺，但如果你想好了，你会学得更好。请注意，这件事并不容易，也不适合胆小的人。但是，你所坚持的可怕的架构使这种事情比它需要的困难了大约一百万倍