I have a query returning the data I needed but the time is overlapping the others.
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)
AND t1.IdCie = t2.IdCie
AND t1.IdBranch = t2.IdBranch
AND t1.IdResource = t2.IdResource
WHERE
t1.IdCie = 1 AND t1.IdBranch = 1
AND t1.IdResource = 1
AND t1.SchedulingByIntervalStartDate BETWEEN '2016-12-01' AND '2016-12-31'
)
SELECT *
FROM cte A
WHERE (SELECT SUM(5) FROM RDV_tbSchedulingByInterval
WHERE IdSchedulingByInterval BETWEEN A.IdSchedulingByIntervalMin AND A.IdSchedulingByIntervalMax) = @newinterval
ORDER BY a.SchedulingByIntervalStartDate
Here's the result of the query:
MinID MaxID StartDate EndDate
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
492570 492581 2016-12-16 09:50:00.000 2016-12-16 10:50:00.000
492571 492582 2016-12-16 09:55:00.000 2016-12-16 10:55:00.000
the expected output must be :
MinID MaxID StartDate EndDate
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 the time overlapped other how can I do this ?