sql-server - SQL Server 如何将 5 分钟的间隔重新组合为 15 分钟的间隔？

Question

我正在建立一个您可以在线预约的网站。我不会详细解释所有内容，但我有一张表格，上面有我可以预约的时间。分配到 5 分钟的间隔。这是一个例子:

ID      StartDate               EndDate
492548  2016-12-16 08:00:00.000 2016-12-16 08:05:00.000
492549  2016-12-16 08:05:00.000 2016-12-16 08:10:00.000
492550  2016-12-16 08:10:00.000 2016-12-16 08:15:00.000
492551  2016-12-16 08:15:00.000 2016-12-16 08:20:00.000
492552  2016-12-16 08:20:00.000 2016-12-16 08:25:00.000
492553  2016-12-16 08:25:00.000 2016-12-16 08:30:00.000
492554  2016-12-16 08:30:00.000 2016-12-16 08:35:00.000
492555  2016-12-16 08:35:00.000 2016-12-16 08:40:00.000
492556  2016-12-16 08:40:00.000 2016-12-16 08:45:00.000
492557  2016-12-16 08:45:00.000 2016-12-16 08:50:00.000
492558  2016-12-16 08:50:00.000 2016-12-16 08:55:00.000
492559  2016-12-16 08:55:00.000 2016-12-16 09:00:00.000
492560  2016-12-16 09:00:00.000 2016-12-16 09:05:00.000
492561  2016-12-16 09:05:00.000 2016-12-16 09:10:00.000
492562  2016-12-16 09:10:00.000 2016-12-16 09:15:00.000
492563  2016-12-16 09:15:00.000 2016-12-16 09:20:00.000
492564  2016-12-16 09:20:00.000 2016-12-16 09:25:00.000
492565  2016-12-16 09:25:00.000 2016-12-16 09:30:00.000
492566  2016-12-16 09:30:00.000 2016-12-16 09:35:00.000

根据咨询时间，根据咨询原因，我必须将这些行归为一类，并知道 min(IDSchedulingInterval) 和 max(IDSchedulingInterval)。

如果持续时间为 15 分钟，这是我想要的结果示例:

Min(ID) Max(ID) StartDate               EndDate
492548  492550  2016-12-16 08:00:00.000 2016-12-16 08:15:00.000
492551  492553  2016-12-16 08:15:00.000 2016-12-16 08:30:00.000
492554  492556  2016-12-16 08:30:00.000 2016-12-16 08:45:00.000
492557  492559  2016-12-16 08:45:00.000 2016-12-16 09:00:00.000

持续时间可以改变。我不知道如何继续进行此查询..

编辑这是您必须检查的一些异常。这是我的 table

ID      StartDate               EndDate                  Isreserved
492548  2016-12-16 08:00:00.000 2016-12-16 08:05:00.000  0  
492549  2016-12-16 08:05:00.000 2016-12-16 08:10:00.000  0  
492550  2016-12-16 08:10:00.000 2016-12-16 08:15:00.000  0  
492551  2016-12-16 08:15:00.000 2016-12-16 08:20:00.000  0  
492552  2016-12-16 08:20:00.000 2016-12-16 08:25:00.000  0      
492555  2016-12-16 08:35:00.000 2016-12-16 08:40:00.000  0  
492556  2016-12-16 08:40:00.000 2016-12-16 08:45:00.000  0  
492557  2016-12-16 08:45:00.000 2016-12-16 08:50:00.000  1  
492558  2016-12-16 08:50:00.000 2016-12-16 08:55:00.000  1  
492559  2016-12-16 08:55:00.000 2016-12-16 09:00:00.000  1  
492560  2016-12-16 09:00:00.000 2016-12-16 09:05:00.000  0  
492561  2016-12-16 09:05:00.000 2016-12-16 09:10:00.000  0  
492562  2016-12-16 09:10:00.000 2016-12-16 09:15:00.000  0  
492563  2016-12-16 09:15:00.000 2016-12-16 09:20:00.000  0  
492564  2016-12-16 09:20:00.000 2016-12-16 09:25:00.000  0  
492565  2016-12-16 09:25:00.000 2016-12-16 09:30:00.000  0  
492566  2016-12-16 09:30:00.000 2016-12-16 09:35:00.000  0  

这里8:45到9:00的时间是预留的，不能坐。此外，您在 8:25 到 8:35 之间没有时间，因此您也无法预订。举个例子，如果我想预约 30 分钟，那么我应该得到这样的结果:

Min(ID) Max(ID) StartDate               EndDate
492560  492565  2016-12-16 09:00:00.000 2016-12-16 09:30:00.000

将只返回 1 行，因为您在其他间隔之间没有足够的时间

编辑 2

多亏了 DVT，我修改了查询，我的查询几乎可以正常工作，这里唯一的问题是重叠时间。这是我的查询:

DECLARE @newinterval INT = 60;

;with cte as (
SELECT
    t1.IdSchedulingByInterval AS IdSchedulingByIntervalMin
    , t2.IdSchedulingByInterval AS IdSchedulingByIntervalMax
    , t1.SchedulingByIntervalStartDate 
    , t2.SchedulingByIntervalEndDate
FROM
   RDV_tbSchedulingByInterval t1
    JOIN RDV_tbSchedulingByInterval t2 ON t2.SchedulingByIntervalStartDate = DATEADD(minute, @newinterval - 5, t1.SchedulingByIntervalStartDate)
    ) select * from cte where (select SUM(5) from RDV_tbSchedulingByInterval where IdSchedulingByInterval 
                                between cte.IdSchedulingByIntervalMin  and cte.IdSchedulingByIntervalMax ) = @newinterval
    order by cte.SchedulingByIntervalStartDate

这是我的结果:

492551  492562  2016-12-16 08:15:00.000 2016-12-16 09:15:00.000
492552  492563  2016-12-16 08:20:00.000 2016-12-16 09:20:00.000
492553  492564  2016-12-16 08:25:00.000 2016-12-16 09:25:00.000
492554  492565  2016-12-16 08:30:00.000 2016-12-16 09:30:00.000
492555  492566  2016-12-16 08:35:00.000 2016-12-16 09:35:00.000
492556  492567  2016-12-16 08:40:00.000 2016-12-16 09:40:00.000
492557  492568  2016-12-16 08:45:00.000 2016-12-16 09:45:00.000
492558  492569  2016-12-16 08:50:00.000 2016-12-16 09:50:00.000
492559  492570  2016-12-16 08:55:00.000 2016-12-16 09:55:00.000
492560  492571  2016-12-16 09:00:00.000 2016-12-16 10:00:00.000
492561  492572  2016-12-16 09:05:00.000 2016-12-16 10:05:00.000
492562  492573  2016-12-16 09:10:00.000 2016-12-16 10:10:00.000
492563  492574  2016-12-16 09:15:00.000 2016-12-16 10:15:00.000
492564  492575  2016-12-16 09:20:00.000 2016-12-16 10:20:00.000
492565  492576  2016-12-16 09:25:00.000 2016-12-16 10:25:00.000
492566  492577  2016-12-16 09:30:00.000 2016-12-16 10:30:00.000
492567  492578  2016-12-16 09:35:00.000 2016-12-16 10:35:00.000
492568  492579  2016-12-16 09:40:00.000 2016-12-16 10:40:00.000
492569  492580  2016-12-16 09:45:00.000 2016-12-16 10:45:00.000

预期结果:

492551  492562  2016-12-16 08:15:00.000 2016-12-16 09:15:00.000
492563  492574  2016-12-16 09:15:00.000 2016-12-16 10:15:00.000

我不想让时间与其他人重叠

Answer 1

-- This converts the period to date-time format
SELECT 
    -- note the 15, the "minute", and the starting point to convert the 
    -- period back to original time
    DATEADD(minute, AP.FifteenMinutePeriod * 15, '2010-01-01T00:00:00') AS Period,
    AP.AvgValue
FROM
    -- this groups by the period and gets the average
    (SELECT
        P.FifteenMinutePeriod,
        AVG(P.Value) AS AvgValue
    FROM
        -- This calculates the period (fifteen minutes in this instance)
        (SELECT 
            -- note the division by 15 and the "minute" to build the 15 minute periods
            -- the '2010-01-01T00:00:00' is the starting point for the periods
            datediff(minute, '2010-01-01T00:00:00', T.Time)/15 AS FifteenMinutePeriod,
            T.Value
        FROM Test T) AS P
    GROUP BY P.FifteenMinutePeriod) AP