使用24小时时间从彼此中减去重叠的预约时间

Question

我正在对一个包含不同临床医生预约信息的表运行查询。每一行都是一个不同的时间块，可以指定不同的名称(例如约会、可用、不可用)它包括一个日期字段和两个用于开始时间和结束时间的字段，这两个字段都存储为整数，并以24小时制格式化(我对数据库没有任何控制，所以我不能将其更改为常规的日期时间格式)从我的上一个问题我已经知道如何将它们转换为标准时间并从那里计算约会的持续时间。

Figuring out the difference in hours between two 24-hour times in SQL

我需要能够弄清楚临床医生在接下来的一周里有多少空闲时间。系统的设置方式是，他们将时间块指定为“可用”，然后人们可以在可用时间之外填写约会。可用时间和每个约会将作为它自己的行输入到数据库中，“可用时间”或“约会”将被指定为时间块的“类型”。

我在尝试计算可用时间时遇到的问题是，尽管约会是在可用时间内安排的，但该可用时间在数据库中的行将保持不变。因此，如果存在从800到1430可用时间的行，以及同一天同一临床医生从800到1000的预约，则可用时间的行仍将保持为800到1430。我需要一种方法，当他们与他们重叠时，从可用时间块中减去约会。因此，在前面的示例中，它会说临床医生仍然有4.5小时的可用时间，而不是有8.5小时的可用时间。可能在某些情况下，预约日期与临床医生的可用时间不重叠，因此仅减去这两个时间并不总是有效的。

这是一个数据外观的示例，以及我对它的当前查询：

http://sqlfiddle.com/#!3/a110e/1/0

我在Microsoft SQL Server 2008中工作。

谢谢!

Answer 1

试试这个：

http://sqlfiddle.com/#!3/a110e/8

SELECT
ap.Staff,
ap.Date,
SUM(CAST((((EndTime % 100 + (EndTime / 100) * 60) - (BeginTime % 100 + (BeginTime / 100) * 60)) / 60.0) as DECIMAL(5,2))) as AvailableTime,
ud.Usedtime
FROM Appointments ap
JOIN 
(
  SELECT
  Staff,
  Date,
  SUM(CAST((((EndTime % 100 + (EndTime / 100) * 60) - (BeginTime % 100 + (BeginTime / 100) * 60)) / 60.0) as DECIMAL(5,2))) as UsedTime
  FROM Appointments ap
  WHERE Type <> 'Available'
  GROUP BY Staff, Date
  )  ud on ap.staff=ud.staff and ap.date=ud.date

WHERE Type = 'Available'

GROUP BY ap.Staff, ap.Date,ud.UsedTime