SQL Server sélectionne le meilleur time disponible

Ci-dessous, ma table des intervalles de time qui indique le time réservé depuis la colonne isReserved – 0 n'est pas réservé. J'ai toutes les minutes disponibles dans ce tableau.

entrez la description de l'image ici

Je dois sélectionner le meilleur time disponible à partir de la table.

Exemple:
Si je dois réserver un travail de 4 minutes, il doit returnner la première heure disponible, et lorsque je dois réserver un travail de 30 minutes, la requête ne doit pas renvoyer la première disponible car il n'y a pas assez de minutes disponibles dans cet intervalle. returnner le prochain premier disponible.

J'utilise sql server 2008 r2

Avec la requête suivante Sélectionnez timelot from jpcbtimeslots où timeslot> @starttime et timeslot <= @endtime et isreserved = '0'

 CREATE TABLE [dbo].[JPCBTimeSlots]( [ID] [bigint] IDENTITY(1,1) NOT NULL, [BayID] [int] NULL, [TimeSlot] [datetime] NULL, [IsReserved] [int] NULL, CONSTRAINT [PK_JPCBTimeSlots] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET IDENTITY_INSERT [dbo].[JPCBTimeSlots] ON INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (1, 1, CAST(0x0000A62200AC3392 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (2, 1, CAST(0x0000A62200AC79E2 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (3, 1, CAST(0x0000A62200ACC032 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (4, 1, CAST(0x0000A62200AD0682 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (5, 1, CAST(0x0000A62200AD4CD2 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (6, 1, CAST(0x0000A62200AD9322 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (7, 1, CAST(0x0000A62200ADD972 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (8, 1, CAST(0x0000A62200AE1FC2 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (9, 1, CAST(0x0000A62200AE6612 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (10, 1, CAST(0x0000A62200AEAC62 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (11, 1, CAST(0x0000A62200AEF2B2 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (12, 1, CAST(0x0000A62200AF3902 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (13, 1, CAST(0x0000A62200AF7F52 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (14, 1, CAST(0x0000A62200AFC5A2 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (15, 1, CAST(0x0000A62200B00BF2 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (16, 1, CAST(0x0000A62200B05242 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (17, 1, CAST(0x0000A62200B09892 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (18, 1, CAST(0x0000A62200B0DEE2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (19, 1, CAST(0x0000A62200B12532 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (20, 1, CAST(0x0000A62200B16B82 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (21, 1, CAST(0x0000A62200B1B1D2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (22, 1, CAST(0x0000A62200B1F822 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (23, 1, CAST(0x0000A62200B23E72 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (24, 1, CAST(0x0000A62200B284C2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (25, 1, CAST(0x0000A62200B2CB12 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (26, 1, CAST(0x0000A62200B31162 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (27, 1, CAST(0x0000A62200B357B2 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (28, 1, CAST(0x0000A62200B39E02 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (29, 1, CAST(0x0000A62200B3E452 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (30, 1, CAST(0x0000A62200B42AA2 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (31, 1, CAST(0x0000A62200B470F2 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (32, 1, CAST(0x0000A62200B4B742 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (33, 1, CAST(0x0000A62200B4FD92 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (34, 1, CAST(0x0000A62200B543E2 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (35, 1, CAST(0x0000A62200B58A32 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (36, 1, CAST(0x0000A62200B5D082 AS DateTime), 1) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (37, 1, CAST(0x0000A62200B616D2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (38, 1, CAST(0x0000A62200B65D22 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (39, 1, CAST(0x0000A62200B6A372 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (40, 1, CAST(0x0000A62200B6E9C2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (41, 1, CAST(0x0000A62200B73012 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (42, 1, CAST(0x0000A62200B77662 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (43, 1, CAST(0x0000A62200B7BCB2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (44, 1, CAST(0x0000A62200B80302 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (45, 1, CAST(0x0000A62200B84952 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (46, 1, CAST(0x0000A62200B88FA2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (47, 1, CAST(0x0000A62200B8D5F2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (48, 1, CAST(0x0000A62200B91C42 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (49, 1, CAST(0x0000A62200B96292 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (50, 1, CAST(0x0000A62200B9A8E2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (51, 1, CAST(0x0000A62200B9EF32 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (52, 1, CAST(0x0000A62200BA3582 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (53, 1, CAST(0x0000A62200BA7BD2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (54, 1, CAST(0x0000A62200BAC222 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (55, 1, CAST(0x0000A62200BB0872 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (56, 1, CAST(0x0000A62200BB4EC2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (57, 1, CAST(0x0000A62200BB9512 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (58, 1, CAST(0x0000A62200BBDB62 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (59, 1, CAST(0x0000A62200BC21B2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (60, 1, CAST(0x0000A62200BC6802 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (61, 1, CAST(0x0000A62200BCAE52 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (62, 1, CAST(0x0000A62200BCF4A2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (63, 1, CAST(0x0000A62200BD3AF2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (64, 1, CAST(0x0000A62200BD8142 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (65, 1, CAST(0x0000A62200BDC792 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (66, 1, CAST(0x0000A62200BE0DE2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (67, 1, CAST(0x0000A62200BE5432 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (68, 1, CAST(0x0000A62200BE9A82 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (69, 1, CAST(0x0000A62200BEE0D2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (70, 1, CAST(0x0000A62200BF2722 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (71, 1, CAST(0x0000A62200BF6D72 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (72, 1, CAST(0x0000A62200BFB3C2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (73, 1, CAST(0x0000A62200BFFA12 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (74, 1, CAST(0x0000A62200C04062 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (75, 1, CAST(0x0000A62200C086B2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (76, 1, CAST(0x0000A62200C0CD02 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (77, 1, CAST(0x0000A62200C11352 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (78, 1, CAST(0x0000A62200C159A2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (79, 1, CAST(0x0000A62200C19FF2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (80, 1, CAST(0x0000A62200C1E642 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (81, 1, CAST(0x0000A62200C22C92 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (82, 1, CAST(0x0000A62200C272E2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (83, 1, CAST(0x0000A62200C2B932 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (84, 1, CAST(0x0000A62200C2FF82 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (85, 1, CAST(0x0000A62200C345D2 AS DateTime), 0) INSERT [dbo].[JPCBTimeSlots] ([ID], [BayID], [TimeSlot], [IsReserved]) VALUES (50435, 35, CAST(0x0000A62300AC3392 AS DateTime), 0) SET IDENTITY_INSERT [dbo].[JPCBTimeSlots] OFF /****** Object: Default [DF_JPCBTimeSlots_IsReserved] Script Date: 06/11/2016 17:59:00 ******/ ALTER TABLE [dbo].[JPCBTimeSlots] ADD CONSTRAINT [DF_JPCBTimeSlots_IsReserved] DEFAULT ((0)) FOR [IsReserved] GO

Vous pouvez get les longueurs totales des intervalles de time en utilisant row_number pour déterminer les espaces et les îlots, puis utiliser le filtrage basé sur cela.

Installer:

 CREATE TABLE #TimeSlots ([X] int, [ID] int, [BaylD] int, [TimeSlot] varchar(23), [IsReserved] int) ; INSERT INTO #TimeSlots ([X], [ID], [BaylD], [TimeSlot], [IsReserved]) VALUES (10, 10, 1, '2016-06-11 10:35:57.980', 1), (11, 11, 1, '2016-06-11 10:36:57.980', 1), (12, 12, 1, '2016-06-11 10:37:57.980', 0), (13, 13, 1, '2016-06-11 10:38:57.980', 0), (14, 14, 1, '2016-06-11 10:39:57.980', 1), (15, 15, 1, '2016-06-11 10:40:57.980', 1), (16, 16, 1, '2016-06-11 10:41:57.980', 0), (17, 17, 1, '2016-06-11 10:42:57.980', 0), (18, 18, 1, '2016-06-11 10:43:57.980', 0), (19, 19, 1, '2016-06-11 10:44:57.980', 0), (20, 20, 1, '2016-06-11 10:45:57.980', 0), (21, 21, 1, '2016-06-11 10:46:57.980', 0), (22, 22, 1, '2016-06-11 10:47:57.980', 0), (23, 23, 1, '2016-06-11 10:48:57.980', 0), (24, 24, 1, '2016-06-11 10:49:57.980', 0), (25, 25, 1, '2016-06-11 10:50:57.980', 0), (26, 26, 1, '2016-06-11 10:51:57.980', 1), (27, 27, 1, '2016-06-11 10:52:57.980', 1), (28, 28, 1, '2016-06-11 10:53:57.980', 1), (29, 29, 1, '2016-06-11 10:54:57.980', 1), (30, 30, 1, '2016-06-11 10:55:57.980', 0), (31, 31, 1, '2016-06-11 10:56:57.980', 0), (32, 32, 1, '2016-06-11 10:57:57.980', 0), (33, 33, 1, '2016-06-11 10:58:57.980', 0), (34, 34, 1, '2016-06-11 10:59:57.980', 1), (35, 35, 1, '2016-06-11 11:00:57.980', 1), (36, 36, 1, '2016-06-11 11:01:57.980', 1), (37, 37, 1, '2016-06-11 11:02:57.980', 0), (38, 38, 1, '2016-06-11 11:03:57.980', 0), (39, 39, 1, '2016-06-11 11:04:57.980', 0) ;

J'ai ajouté quelques slots libres supplémentaires pour que le SQL returnne plus de lignes.

La requête:

 select min(TimeSlot), max(TimeSlot), datediff(minute, min(TimeSlot), max(TimeSlot)) from ( select row_number() over (order by TimeSlot) as RN1, row_number() over (partition by IsReserved order by TimeSlot) as RN2, * from #TimeSlots ) X where IsReserved = 0 group by RN1 - RN2

Données renvoyées:

 Start End Duration 2016-06-11 10:37:57.980 2016-06-11 10:38:57.980 1 2016-06-11 10:41:57.980 2016-06-11 10:50:57.980 9 2016-06-11 10:55:57.980 2016-06-11 10:58:57.980 3 2016-06-11 11:02:57.980 2016-06-11 11:04:57.980 2

Cela montre bien sûr les locations libres, donc vous devrez append 1 minute à la durée

Commençons par créer une table temporaire et la remplir avec des données de test.

 CREATE TABLE #tmpTimeSlots (ID int, TimeSlot datetime, IsReserved int); INSERT INTO #tmpTimeSlots (ID, TimeSlot, IsReserved) VALUES (10,'2016-06-11 10:35:00',1),(11,'2016-06-11 10:36:00',1),(12,'2016-06-11 10:37:00',0), (13,'2016-06-11 10:38:00',0),(14,'2016-06-11 10:39:00',1),(15,'2016-06-11 10:40:00',1), (16,'2016-06-11 10:41:00',0),(17,'2016-06-11 10:42:00',0),(18,'2016-06-11 10:43:00',0), (19,'2016-06-11 10:44:00',0),(20,'2016-06-11 10:45:00',0),(21,'2016-06-11 10:46:00',0), (22,'2016-06-11 10:47:00',0),(23,'2016-06-11 10:48:00',0),(24,'2016-06-11 10:49:00',0), (25,'2016-06-11 10:50:00',0),(26,'2016-06-11 10:51:00',1),(27,'2016-06-11 10:52:00',1), (28,'2016-06-11 10:53:00',1),(29,'2016-06-11 10:54:00',1),(30,'2016-06-11 10:55:00',0), (31,'2016-06-11 10:56:00',0),(32,'2016-06-11 10:57:00',0),(33,'2016-06-11 10:58:00',0), (34,'2016-06-11 10:59:00',1),(35,'2016-06-11 11:00:00',1),(36,'2016-06-11 11:01:00',1), (37,'2016-06-11 11:02:00',0),(38,'2016-06-11 11:03:00',0),(39,'2016-06-11 11:04:00',0);

Cette requête devrait fonctionner sur sql-server 2008.
Bien que je ne pouvais le tester sur une version plus récente.

 WITH Q1 AS ( select TimeSlot as time, IsReserved as flag, row_number() over (order by TimeSlot) as rn from #tmpTimeSlots ), Q2 AS ( select t1.time as time1, coalesce(t2.time,getdate()) as time2, t1.flag as flag1, coalesce(t2.flag,-1) as flag2, row_number() over (order by t1.time) as rn from Q1 t1 left join Q1 t2 on (t1.rn = t2.rn-1) where t1.flag <> coalesce(t2.flag,-1) ) select start_time, end_time, diff_minutes from ( select q1.time2 as start_time, q2.time1 as end_time, DATEDIFF(minute, q1.time2, q2.time1) as diff_minutes from Q2 q1 join Q2 q2 on (q1.rn = q2.rn-1) where q1.flag2 = 0 and q2.flag1 = 0 ) Q3 --where diff_minutes >= 4 --and start_time >= @starttime and end_time <= @endtime order by start_time;

Pour les critères de la plage de time peut être décommenté si cela est requirejs.

Pour datatables de test, il returnnera ces résultats:

 start_time end_time diff_minutes 11.06.2016 10:37:00 11.06.2016 10:38:00 1 11.06.2016 10:41:00 11.06.2016 10:50:00 9 11.06.2016 10:55:00 11.06.2016 10:58:00 3 11.06.2016 11:02:00 11.06.2016 11:04:00 2

Avec SQL Server 2012 et au-delà, la fonction analytique LEAD devient disponible.
Ce qui simplifie un peu les choses.

 select start_time, end_time, DATEDIFF(minute, start_time, end_time) as diff_minutes from ( select dateadd(minute,1,time) as start_time, dateadd(minute,-1,next_time) as end_time from ( select TimeSlot as time, IsReserved as flag, LEAD(TimeSlot, 1) OVER(PARTITION BY IsReserved ORDER BY TimeSlot) as next_time from #tmpTimeSlots ) Q1 where flag = 1 ) Q2 where start_time < coalesce(end_time,getdate()) --and DATEDIFF(minute, start_time, end_time) >= 4 --and start_time >= @starttime and end_time <= @endtime order by start_time;