SQL Server select best available time -
below time slots table indicates reserved time isreserved column - 0 not reserved. have day minutes available in table.
i need select best available time based table.
example:
if need book 4 minutes job, should return first available time, , when need book 30 minutes job, query should not return first available there not enough minutes available in timeslot , return next first available.
i using sql server 2008 r2
with following query select timeslot jpcbtimeslots timeslot > @starttime , timeslot<= @endtime , 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 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (2, 1, cast(0x0000a62200ac79e2 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (3, 1, cast(0x0000a62200acc032 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (4, 1, cast(0x0000a62200ad0682 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (5, 1, cast(0x0000a62200ad4cd2 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (6, 1, cast(0x0000a62200ad9322 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (7, 1, cast(0x0000a62200add972 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (8, 1, cast(0x0000a62200ae1fc2 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (9, 1, cast(0x0000a62200ae6612 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (10, 1, cast(0x0000a62200aeac62 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (11, 1, cast(0x0000a62200aef2b2 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (12, 1, cast(0x0000a62200af3902 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (13, 1, cast(0x0000a62200af7f52 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (14, 1, cast(0x0000a62200afc5a2 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (15, 1, cast(0x0000a62200b00bf2 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (16, 1, cast(0x0000a62200b05242 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (17, 1, cast(0x0000a62200b09892 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (18, 1, cast(0x0000a62200b0dee2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (19, 1, cast(0x0000a62200b12532 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (20, 1, cast(0x0000a62200b16b82 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (21, 1, cast(0x0000a62200b1b1d2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (22, 1, cast(0x0000a62200b1f822 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (23, 1, cast(0x0000a62200b23e72 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (24, 1, cast(0x0000a62200b284c2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (25, 1, cast(0x0000a62200b2cb12 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (26, 1, cast(0x0000a62200b31162 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (27, 1, cast(0x0000a62200b357b2 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (28, 1, cast(0x0000a62200b39e02 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (29, 1, cast(0x0000a62200b3e452 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (30, 1, cast(0x0000a62200b42aa2 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (31, 1, cast(0x0000a62200b470f2 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (32, 1, cast(0x0000a62200b4b742 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (33, 1, cast(0x0000a62200b4fd92 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (34, 1, cast(0x0000a62200b543e2 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (35, 1, cast(0x0000a62200b58a32 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (36, 1, cast(0x0000a62200b5d082 datetime), 1) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (37, 1, cast(0x0000a62200b616d2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (38, 1, cast(0x0000a62200b65d22 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (39, 1, cast(0x0000a62200b6a372 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (40, 1, cast(0x0000a62200b6e9c2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (41, 1, cast(0x0000a62200b73012 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (42, 1, cast(0x0000a62200b77662 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (43, 1, cast(0x0000a62200b7bcb2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (44, 1, cast(0x0000a62200b80302 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (45, 1, cast(0x0000a62200b84952 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (46, 1, cast(0x0000a62200b88fa2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (47, 1, cast(0x0000a62200b8d5f2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (48, 1, cast(0x0000a62200b91c42 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (49, 1, cast(0x0000a62200b96292 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (50, 1, cast(0x0000a62200b9a8e2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (51, 1, cast(0x0000a62200b9ef32 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (52, 1, cast(0x0000a62200ba3582 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (53, 1, cast(0x0000a62200ba7bd2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (54, 1, cast(0x0000a62200bac222 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (55, 1, cast(0x0000a62200bb0872 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (56, 1, cast(0x0000a62200bb4ec2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (57, 1, cast(0x0000a62200bb9512 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (58, 1, cast(0x0000a62200bbdb62 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (59, 1, cast(0x0000a62200bc21b2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (60, 1, cast(0x0000a62200bc6802 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (61, 1, cast(0x0000a62200bcae52 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (62, 1, cast(0x0000a62200bcf4a2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (63, 1, cast(0x0000a62200bd3af2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (64, 1, cast(0x0000a62200bd8142 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (65, 1, cast(0x0000a62200bdc792 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (66, 1, cast(0x0000a62200be0de2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (67, 1, cast(0x0000a62200be5432 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (68, 1, cast(0x0000a62200be9a82 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (69, 1, cast(0x0000a62200bee0d2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (70, 1, cast(0x0000a62200bf2722 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (71, 1, cast(0x0000a62200bf6d72 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (72, 1, cast(0x0000a62200bfb3c2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (73, 1, cast(0x0000a62200bffa12 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (74, 1, cast(0x0000a62200c04062 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (75, 1, cast(0x0000a62200c086b2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (76, 1, cast(0x0000a62200c0cd02 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (77, 1, cast(0x0000a62200c11352 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (78, 1, cast(0x0000a62200c159a2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (79, 1, cast(0x0000a62200c19ff2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (80, 1, cast(0x0000a62200c1e642 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (81, 1, cast(0x0000a62200c22c92 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (82, 1, cast(0x0000a62200c272e2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (83, 1, cast(0x0000a62200c2b932 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (84, 1, cast(0x0000a62200c2ff82 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (85, 1, cast(0x0000a62200c345d2 datetime), 0) insert [dbo].[jpcbtimeslots] ([id], [bayid], [timeslot], [isreserved]) values (50435, 35, cast(0x0000a62300ac3392 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)) [isreserved] go
you can the total lengths of timeslots using row_number figure out gaps , islands use filtering based on that.
setup:
create table #timeslots ([x] int, [id] int, [bayld] int, [timeslot] varchar(23), [isreserved] int) ; insert #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) ; i added few more free slots make sql return more rows.
the query:
select min(timeslot), max(timeslot), datediff(minute, min(timeslot), max(timeslot)) ( select row_number() on (order timeslot) rn1, row_number() on (partition isreserved order timeslot) rn2, * #timeslots ) x isreserved = 0 group rn1 - rn2 returned data:
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 this of course shows free slots, you'll need add 1 minute duration
Comments
Post a Comment