SQL Server how to regroup interval of 5 minute into 1 of 15 minute?

Question

I'm building up a website where you can take an appointement online. I'll not explain all in detail but I have an table with my available time to take an appointment. Distributed into intervals of 5 min. Here's an example:

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

Depending the consultation time, based in the reason for consultation, I have to group these rows into one and know the min(IDSchedulingInterval) and the max(IDSchedulingInterval).

Here's an example of the result I want if I have a duration time of 15 min:

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

The duration time can change. I don't know how to proceed to make this query..

EDIT Here are some exception you have to check. Here's my 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  

Here the time between 8:45 to 9:00 is reserved so you can't take it. Also you don't have time between 8:25 and 8:35 so you can't reserved it either. An example, if I want to take a appointment of 30 min then I should have a result like this one:

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

Only 1 row will be returned because you don't have enough time between other intervals

EDIT 2

Thanks to DVT I have modified is query and i'm almost having my query work the only hic here is the overlapping time. here's my query:

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

Here's my result:

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

Expected result:

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

I don't want time to overlapped other

Answer 1

DECLARE @newinterval INT = 15;

SELECT
    t1.IdSchedulingByInterval AS IdSchedulingByIntervalMin
    , t2.IdSchedulingByInterval AS IdSchedulingByIntervalMax
    , t1.SchedulingByIntervalStartDate 
    , t2.SchedulingByIntervalEndDate
FROM
    <table> t1
    JOIN <table> t2 ON t2.SchedulingByIntervalStartDate = DATEADD(minute, @newinterval - 5, t1.SchedulingByIntervalStartDate)
WHERE
    DATEPART(minute,t1.SchedulingByIntervalStartDate) % @newinterval = 0;

Answer 2

As your session duration can change, here is a slightly more flexible approach:

declare @t table (IDSchedulingByInterval int identity(1,1)
                    ,SchedulingByIntervalStartDate datetime
                    ,SchedulingByIntervalEndDate datetime
                    );
insert into @t(SchedulingByIntervalStartDate, SchedulingByIntervalEndDate)
values('2016-12-16 08:00:00.000','2016-12-16 08:05:00.000'),('2016-12-16 08:05:00.000','2016-12-16 08:10:00.000'),('2016-12-16 08:10:00.000','2016-12-16 08:15:00.000'),('2016-12-16 08:15:00.000','2016-12-16 08:20:00.000'),('2016-12-16 08:20:00.000','2016-12-16 08:25:00.000'),('2016-12-16 08:25:00.000','2016-12-16 08:30:00.000'),('2016-12-16 08:30:00.000','2016-12-16 08:35:00.000'),('2016-12-16 08:35:00.000','2016-12-16 08:40:00.000'),('2016-12-16 08:40:00.000','2016-12-16 08:45:00.000'),('2016-12-16 08:45:00.000','2016-12-16 08:50:00.000'),('2016-12-16 08:50:00.000','2016-12-16 08:55:00.000'),('2016-12-16 08:55:00.000','2016-12-16 09:00:00.000'),('2016-12-16 09:00:00.000','2016-12-16 09:05:00.000'),('2016-12-16 09:05:00.000','2016-12-16 09:10:00.000'),('2016-12-16 09:10:00.000','2016-12-16 09:15:00.000'),('2016-12-16 09:15:00.000','2016-12-16 09:20:00.000'),('2016-12-16 09:20:00.000','2016-12-16 09:25:00.000'),('2016-12-16 09:25:00.000','2016-12-16 09:30:00.000'),('2016-12-16 09:30:00.000','2016-12-16 09:35:00.000'),('2016-12-16 09:35:00.000','2016-12-16 09:40:00.000'),('2016-12-16 09:40:00.000','2016-12-16 09:45:00.000'),('2016-12-16 09:45:00.000','2016-12-16 09:50:00.000'),('2016-12-16 09:50:00.000','2016-12-16 09:55:00.000'),('2016-12-16 09:55:00.000','2016-12-16 10:00:00.000');

declare @Interval int = 15; -- This is the number of minutes for each session.  Must be divisible by 5 as base data is at a 5 minute granualarity.

select s.IDSchedulingByInterval as MinIDSchedulingByInterval
        ,e.IDSchedulingByInterval as MaxIDSchedulingByInterval
        ,s.SchedulingByIntervalStartDate
        ,e.SchedulingByIntervalEndDate
from @t s
    left join @t e  -- Find the corresponding end time for the session's start time
        on(dateadd(minute,@Interval,s.SchedulingByIntervalStartDate) =  e.SchedulingByIntervalEndDate)
where datediff(minute
                ,(select min(SchedulingByIntervalStartDate) from @t)
                ,s.SchedulingByIntervalStartDate
                ) % @Interval = 0;      -- This is the check that start time is at the start of one of your defined intervals.

Updated to include logic for unavailable periods:

declare @t table (IDSchedulingByInterval int identity(1,1)
                    ,SchedulingByIntervalStartDate datetime
                    ,SchedulingByIntervalEndDate datetime
                    ,Reserved bit
                    );
insert into @t(SchedulingByIntervalStartDate, SchedulingByIntervalEndDate,Reserved)
values('2016-12-16 08:00:00.000','2016-12-16 08:05:00.000',0),('2016-12-16 08:05:00.000','2016-12-16 08:10:00.000',0),('2016-12-16 08:10:00.000','2016-12-16 08:15:00.000',0),('2016-12-16 08:15:00.000','2016-12-16 08:20:00.000',0),('2016-12-16 08:20:00.000','2016-12-16 08:25:00.000',0),('2016-12-16 08:25:00.000','2016-12-16 08:30:00.000',0),('2016-12-16 08:30:00.000','2016-12-16 08:35:00.000',0),('2016-12-16 08:35:00.000','2016-12-16 08:40:00.000',0),('2016-12-16 08:40:00.000','2016-12-16 08:45:00.000',0),('2016-12-16 08:45:00.000','2016-12-16 08:50:00.000',1),('2016-12-16 08:50:00.000','2016-12-16 08:55:00.000',1),('2016-12-16 08:55:00.000','2016-12-16 09:00:00.000',1),('2016-12-16 09:00:00.000','2016-12-16 09:05:00.000',0),('2016-12-16 09:05:00.000','2016-12-16 09:10:00.000',0),('2016-12-16 09:10:00.000','2016-12-16 09:15:00.000',0),('2016-12-16 09:15:00.000','2016-12-16 09:20:00.000',0),('2016-12-16 09:20:00.000','2016-12-16 09:25:00.000',0),('2016-12-16 09:25:00.000','2016-12-16 09:30:00.000',0),('2016-12-16 09:30:00.000','2016-12-16 09:35:00.000',0),('2016-12-16 09:35:00.000','2016-12-16 09:40:00.000',0),('2016-12-16 09:40:00.000','2016-12-16 09:45:00.000',0),('2016-12-16 09:45:00.000','2016-12-16 09:50:00.000',0),('2016-12-16 09:50:00.000','2016-12-16 09:55:00.000',0),('2016-12-16 09:55:00.000','2016-12-16 10:00:00.000',0);

declare @Interval int = 60; -- This is the number of minutes for each session.  Must be divisible by 5 as base data is at a 5 minute granualarity.

with cte
as
(
    select s.IDSchedulingByInterval as MinIDSchedulingByInterval
            ,e.IDSchedulingByInterval as MaxIDSchedulingByInterval
            ,s.SchedulingByIntervalStartDate
            ,e.SchedulingByIntervalEndDate
    from @t s
        left join @t e  -- Find the corresponding end time for the session's start time
            on(dateadd(minute,@Interval,s.SchedulingByIntervalStartDate) =  e.SchedulingByIntervalEndDate)
    where datediff(minute
                    ,(select min(SchedulingByIntervalStartDate) from @t)
                    ,s.SchedulingByIntervalStartDate
                    ) % @Interval = 0       -- This is the check that start time is at the start of one of your defined intervals.
)
select c.MinIDSchedulingByInterval
        ,c.MaxIDSchedulingByInterval
        ,c.SchedulingByIntervalStartDate
        ,c.SchedulingByIntervalEndDate
from cte c
    left join @t t
        on(t.SchedulingByIntervalStartDate <= c.SchedulingByIntervalEndDate
            and t.SchedulingByIntervalEndDate > c.SchedulingByIntervalStartDate
            )
group by c.MinIDSchedulingByInterval
        ,c.MaxIDSchedulingByInterval
        ,c.SchedulingByIntervalStartDate
        ,c.SchedulingByIntervalEndDate
having sum(cast(t.Reserved as int)) = 0

Answer 3

-- 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

Answer 4

The simplest query I can think of..

    select MIN(ID), MAX(ID), MIN(StartDate), MAX(EndDate)
    from 
        (
        select 
            ID,
            case
               when substring(CONVERT(varchar, StartDate),16,2)in ('00','05','10') then 1
               when substring(CONVERT(varchar, StartDate),16,2)in ('15','20','25') then 2
               when substring(CONVERT(varchar, StartDate),16,2)in ('30','35','40') then 3
               when substring(CONVERT(varchar, StartDate),16,2)in ('45','50','55') then 4
            end as MinOfDate,
            substring(CONVERT(varchar, StartDate),13,2)as HourOfDate,
            substring(CONVERT(varchar, GETDATE()),1,6) as DayOfDate
            StartDate,
            EndDate
        from SourceTable
        where IsReserved = 0
        ) t
    group by DayOfDate,HourOfDate,MinOfDate

Answer 5

This handles a variable minute interval. Note this hasn't been performance tested:

DECLARE @MinuteInterval INT = 15

SELECT      MIN(a.IdSchedulingByInterval) AS MinId,
            MAX(aa.IdSchedulingByInterval) AS MaxId,
            MIN(a.SchedulingByIntervalStartDate) AS StartDate,
            MAX(aa.SchedulingByIntervalEndDate) AS EndDate
FROM        Appointment a
CROSS JOIN  Appointment aa 
WHERE       DATEDIFF(MINUTE, a.SchedulingByIntervalStartDate, aa.SchedulingByIntervalEndDate) = @MinuteInterval
GROUP BY    a.IdSchedulingByInterval
HAVING      DATEPART(MINUTE, MIN(a.SchedulingByIntervalStartDate)) % @MinuteInterval = 0

Answer 6

This is the easiest i could come up with without more specifics

;WITH Tally AS
 (
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N FROM master.sys.objects A, master.sys.objects B
 )
,Intervals AS
(
    SELECT
    N AS Id,
    DATEADD(MINUTE, (N-1)*5, '20160101') AS StartDate,
    DATEADD(MINUTE, (N)*5, '20160101') AS EndDate
    FROM Tally
)

SELECT MIN(Id) AS MinId, MAX(Id) AS MaxId, MIN(StartDate), MAX(EndDate) FROM Intervals
GROUP BY CAST(StartDate AS Date), DATEPART(HOUR,StartDate), DATEPART(MINUTE, StartDate) / 15
ORDER BY MinId

EDIT:

Just replace the names with your table like

SELECT 
MIN(IdSchedulingByInterval) AS MinId, 
MAX(IdSchedulingByInterval) AS MaxId, 
MIN(SchedulingByIntervalStartDate), ¨
MAX(SchedulingByIntervalEndDate) 
FROM RDV_tbSchedulingByInterval 
GROUP BY CAST(SchedulingByIntervalStartDate AS Date), DATEPART(HOUR,SchedulingByIntervalStartDate), DATEPART(MINUTE, SchedulingByIntervalStartDate) / 15
ORDER BY MinId

Answer 7

I think you can use a query like the following:

SELECT MIN(ID) AS minID, MAX(ID) AS maxID, 
       MIN(StartDate) AS StartDate, MAX(EndDate) AS EndDate
FROM (
   SELECT ID, StartDate, EndDate,       
          ROW_NUMBER() OVER (ORDER BY StartDate) - 
          ROW_NUMBER() OVER (PARTITION BY x.v
                             ORDER BY StartDate) AS grp
   FROM mytable
   CROSS APPLY (SELECT CAST(CONVERT(DATE, EndDate) AS VARCHAR(10)) + 
                       CAST(DATEPART(HOUR, StartDate) AS VARCHAR(10)) + 
                       CAST(DATEPART(MINUTE, StartDate) / 15 AS VARCHAR(10))) AS x(v)) AS t
GROUP BY t.grp                                              
ORDER BY EndDate