我想了解如何将示例 1 中的下表"汇总"到累积可用时间段中，如示例 2 所示。
以下代码按天和半小时时间段返回所有可用位置及其空闲时间。
我需要以周期为单位返回可用时间。 例如，如果某个位置在上午 9 点至下午 5 点之间可用，则应显示。
返回数据的代码：
SELECT
LTB.CalendarDate,
LTB.VenueCode,
LTB.VenueName,
LTB.LocationCode,
LTB.LocationDescription,
LTB.OpenTime,
LTB.CloseTime,
LTB.Weekday,
LTB.WeekdayName,
LTB.MaxUniqueParticipants,
LTB.TimeStartBlock,
LTB.TimeEndBlock
FROM
--LTB
--TimeBlock
(
SELECT LocationsOpenClose.CalendarDate,
LocationsOpenClose.StartTime,
LocationsOpenClose.EndTime,
LocationsOpenClose.VenueCode,
LocationsOpenClose.VenueName,
LocationsOpenClose.LocationCode,
LocationsOpenClose.LocationDescription,
LocationsOpenClose.Weekday,
LocationsOpenClose.WeekdayName,
LocationsOpenClose.MaxUniqueParticipants,
LocationsOpenClose.OpenTime,
LocationsOpenClose.CloseTime,
TimeDimension_2.[Time] AS TimeStartBlock,
DATEADD(SECOND, 1799, TimeDimension_2.[Time])
AS TimeEndBlock
--Locations Open Close
FROM (SELECT
dbo.LocationRoster.CalendarDate,
dbo.LocationRoster.StartTime,
dbo.LocationRoster.EndTime,
dbo.Venues.VenueCode,
dbo.Venues.VenueName,
dbo.Locations.LocationCode,
dbo.Locations.Description AS LocationDescription,
DateDimension.Weekday,
DateDimension.WeekdayName,
dbo.Locations.MaxUniqueParticipants,
TimeDimension.[Time] AS OpenTime,
TimeDimension_1.[Time] AS CloseTime
FROM dbo.LocationRoster
INNER JOIN dbo.Locations ON dbo.LocationRoster.LocationGUID = dbo.Locations.LocationGUID
INNER JOIN DateDimension ON dbo.LocationRoster.CalendarDate = DateDimension.[Date]
INNER JOIN TimeDimension ON dbo.LocationRoster.StartTime = TimeDimension.MinuteFromMidnight
INNER JOIN TimeDimension TimeDimension_1 ON dbo.LocationRoster.EndTime = TimeDimension_1.MinuteFromMidnight
INNER JOIN dbo.Venues ON dbo.Locations.VenueGUID = dbo.Venues.VenueGUID) AS LocationsOpenClose
RIGHT OUTER JOIN TimeDimension TimeDimension_2 ON TimeDimension_2.[Time] BETWEEN LocationsOpenClose.OpenTime AND DATEADD(SECOND, -1, LocationsOpenClose.CloseTime)
WHERE (LocationsOpenClose.CalendarDate BETWEEN '2018/08/15' AND '2018/08/16') ) AS LTB
--CETB
--CalendarEntriesStartFinish
LEFT OUTER JOIN
(
SELECT
CalendarEntriesStartFinish.CalendarEntryDate,
CalendarEntriesStartFinish.VenueCode,
CalendarEntriesStartFinish.VenueName,
CalendarEntriesStartFinish.LocationCode,
CalendarEntriesStartFinish.LocationDescription,
CalendarEntriesStartFinish.BookingStartTime,
CalendarEntriesStartFinish.BookingEndTime,
TimeDimension_2.[Time] AS TimeStartBlock,
DATEADD(SECOND, -1, TimeDimension_2.[Time]) AS TimeEndBlock,
CalendarEntriesStartFinish.ReferenceGUID
FROM
(
SELECT
CONVERT(DATE, dbo.CalendarEntries.StartDateTime) AS CalendarEntryDate,
dbo.Venues.VenueCode,
dbo.Venues.VenueName,
dbo.Locations.LocationCode,
dbo.Locations.Description AS LocationDescription,
CONVERT(TIME, dbo.CalendarEntries.StartDateTime) AS BookingStartTime,
DATEADD(SECOND,-1,CONVERT(TIME, dbo.CalendarEntries.FinishDateTime)) AS BookingEndTime,
dbo.CalendarEntries.ReferenceGUID
FROM dbo.CalendarEntries
INNER JOIN dbo.Locations ON dbo.CalendarEntries.LocationGUID = dbo.Locations.LocationGUID
INNER JOIN dbo.Venues ON dbo.Locations.VenueGUID = dbo.Venues.VenueGUID
WHERE (dbo.CalendarEntries.CalendarEntryType IN (0, 2)) AND (dbo.Locations.IsScheduled = 1)
) AS CalendarEntriesStartFinish
RIGHT OUTER JOIN TimeDimension TimeDimension_2 ON TimeDimension_2.[Time] BETWEEN CalendarEntriesStartFinish.BookingStartTime AND CalendarEntriesStartFinish.BookingEndTime
WHERE (CalendarEntriesStartFinish.CalendarEntryDate BETWEEN '2018/08/15' AND '2018/08/16')
) AS CETB ON LTB.CalendarDate = CONVERT(DATE,CETB.CalendarEntryDate) AND LTB.LocationCode = CETB.LocationCode AND LTB.TimeStartBlock = CETB.TimeStartBlock
WHERE (CETB.CalendarEntryDate IS NULL)
ORDER BY LTB.CalendarDate, LTB.LocationDescription, LTB.TimeStartBlock
示例 1：当前结果
CalendarDate VenueCode VenueName LocationCode LocationDescription OpenTime CloseTime Weekday WeekdayName MaxUniqueParticipants TimeStartBlock TimeEndBlock
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 08:30:00 08:59:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 09:00:00 09:29:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 11:00:00 11:29:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 11:30:00 11:59:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 12:00:00 12:29:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 12:30:00 12:59:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 16:00:00 16:29:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 16:30:00 16:59:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 17:00:00 17:29:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 17:30:00 17:59:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 18:00:00 18:29:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 18:30:00 18:59:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 19:00:00 19:29:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 19:30:00 19:59:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 20:00:00 20:29:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 20:30:00 20:59:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 21:00:00 21:29:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 21:30:00 21:59:59
上面显示了半小时的所有可用时间。 它显示 A 位置在 8 月 15 日的 13：00 至 16：00 不可用。
示例 2：所需结果
CalendarDate VenueCode VenueName LocationCode LocationDescription OpenTime CloseTime Weekday WeekdayName MaxUniqueParticipants TimeStartBlock TimeEndBlock
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 08:30:00 12:59:59
15/08/2018 VC Venue Name Lcode A Location 05:30:00 22:00:00 4 Wednesday 20 16:00:00 21:59:59
以便他们知道 A 位置在 05：30 和 08：30 之间免费，然后在 16：00 到 22：00 之间再次免费
时间维度表以半小时为间隔进行拆分。
一位同事在下面建议，但我无法让它工作。
WITH t AS (
SELECT LTB.CalendarDate d,ROW_NUMBER() OVER(ORDER BY LTB.CalendarDate) i
FROM @d
GROUP BY LTB.CalendarDate
)
SELECT MIN(d),MAX(d)
FROM t
GROUP BY DATEDIFF(day,i,d)
我的SQL并不惊人，所以请进行完整的解释，感谢您的帮助。
这里有一个例子，用我自己的测试数据
--build some sample data
select * into #tempt from
(
SELECT cast('20180515' as date) calendardate, 'VC' as VenueCode, CAST('08:30:00' as time) opentime, CAST('08:59:59' as time) closetime union
SELECT cast('20180515' as date) calendardate, 'VC' as VenueCode, CAST('09:00:00' as time) opentime, CAST('09:29:59' as time) closetime union
SELECT cast('20180515' as date) calendardate, 'VC' as VenueCode, CAST('09:30:00' as time) opentime, CAST('15:59:59' as time) closetime union
SELECT cast('20180515' as date) calendardate, 'VC' as VenueCode, CAST('16:30:00' as time) opentime, CAST('18:30:59' as time) closetime union
SELECT cast('20180515' as date) calendardate, 'VX' as VenueCode, CAST('08:30:00' as time) opentime, CAST('08:59:59' as time) closetime union
SELECT cast('20180515' as date) calendardate, 'VX' as VenueCode, CAST('09:00:00' as time) opentime, CAST('09:29:59' as time) closetime union
SELECT cast('20180515' as date) calendardate, 'VX' as VenueCode, CAST('09:45:00' as time) opentime, CAST('15:59:59' as time) closetime union
SELECT cast('20180515' as date) calendardate, 'VX' as VenueCode, CAST('16:45:00' as time) opentime, CAST('18:30:59' as time) closetime union
SELECT cast('20180515' as date) calendardate, 'VX' as VenueCode, CAST('18:31:00' as time) opentime, CAST('22:30:59' as time) closetime
) dq
--now do the work
--build a 'seconds in day' table using the first 3 CTE tables,
;with secs as (select 0 sc union all select sc + 1 from secs where sc < 59),
hrs as (select 0 hr union all select hr + 1 from hrs where hr < 23),
allt as (select dateadd(SECOND, s1.sc + m1.sc * 60 + h1.hr * 3600, CAST('00:00:00' as time)) t from secs s1, secs m1, hrs h1),
island as (select SQ.VenueCode,allt.t, case when exists(select 0 from #tempt tm WHERE SQ.VenueCode = tm.VenueCode and allt.t BETWEEN tm.opentime and tm.closetime) then 1 else 0 end x from allt cross join (SELECT distinct t3.VenueCode FROM #tempt t3) SQ), --calculation starts here
calc as (select VenueCode,t,x,ROW_NUMBER() over(partition by venuecode order by t) - ROW_NUMBER() over(partition by venuecode,x order by t) z from island) --asigns a number to all in an island
select VenueCode,x,MIN(t),MAX(t) from calc group by VenueCode, x,z having x = 1 order by venuecode, MIN(t) --summarises islands
--clear test data
drop table #tempt;