find available time slot

I have a table that looks like:
CREATE TABLE [dbo].[UserAgenda] (
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL ,
) ON [PRIMARY]
GO
and I would like to find a time gap for the amount of time I am looking
for.
For example, I want the next available hour in the list or the next
available 15 minutes.
Any help would be appreciated.
Thanks
Richard MeetzeAm I correct that "next available" means a period of time that won't
overlap the period between StartTime and EndTime on any row in the
table? Try this:
DECLARE @.m INTEGER
SET @.m = 10
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
David Portas
SQL Server MVP
--|||David - you are correct, thanks for the help.
I have another question if you or anyone else it up for it.
Using the same table schema as above, I have the following data:
Start End
2005-01-01 08:15:00.000 2005-01-01 08:45:00.000
2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
Now I want to place an item in from 8:30 to 8:45 and I want my end
result to look like:
Start End
2005-01-01 08:15:00.000 2005-01-01 08:30:00.000
2005-01-01 08:30:00.000 2005-01-01 08:45:00.000
2005-01-01 08:45:00.000 2005-01-01 09:00:00.000
2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
I had to insert the new time in a current time and split the other time
to two parts and make it fit. Does make since?
Thanks
Richard Meetze|||David,
I think will should check for existence of rows between the matching ones.
Somethnig like:
...
and not exists (select * from UserAgenda as c where c.starttime > a.endtime
and c.endtime < b.starttime)
Example:
create table UserAgenda (
[StartTime] [datetime] NOT NULL ,
[EndTime] [datetime] NOT NULL
)
insert into UserAgenda values('2005-02-23T08:30:00', '2005-02-23T08:35:00')
insert into UserAgenda values('2005-02-23T08:36:00', '2005-02-23T08:46:00')
insert into UserAgenda values('2005-02-23T08:47:00', '2005-02-23T08:57:00')
DECLARE @.m INTEGER
SET @.m = 10
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
FROM UserAgenda AS A
LEFT JOIN UserAgenda AS B
ON A.endtime <= B.starttime
WHERE DATEDIFF(MI,A.endtime,
COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
and not exists (select * from UserAgenda as c where c.starttime > a.endtime
and c.endtime < b.starttime)
drop table UserAgenda
go
AMB
"David Portas" wrote:

> Am I correct that "next available" means a period of time that won't
> overlap the period between StartTime and EndTime on any row in the
> table? Try this:
> DECLARE @.m INTEGER
> SET @.m = 10
> SELECT MIN(A.endtime), DATEADD(MI,@.m,MIN(A.endtime))
> FROM UserAgenda AS A
> LEFT JOIN UserAgenda AS B
> ON A.endtime <= B.starttime
> WHERE DATEDIFF(MI,A.endtime,
> COALESCE(B.starttime,'2100-12-31T23:59:59.997'))>=@.m
> --
> David Portas
> SQL Server MVP
> --
>|||See if it hepls you
create table UserAgenda
(
StartTime datetime not null,
EndTime datetime not null
)
insert into UserAgenda(StartTime,EndTime)values ('20000610 10:00','20000610
10:15')
insert into UserAgenda(StartTime,EndTime)values ('20000610 10:30','20000610
10:50')
insert into UserAgenda(StartTime,EndTime)values ('20000610 11:00','20000610
11:25')
insert into UserAgenda(StartTime,EndTime)values ('20000610 11:45','20000610
11:55')
insert into UserAgenda(StartTime,EndTime)values ('20000610 12:57','20000610
13:00')
insert into UserAgenda(StartTime,EndTime)values ('20000610 13:15','20000610
13:30')
insert into UserAgenda(StartTime,EndTime)values ('20000610 14:04','20000610
14:40')
SELECT
StartTime,
ISNULL(
(SELECT MIN(EndTime)
FROM UserAgenda AS S3
WHERE S3.StartTime >= S1.StartTime
AND ISNULL(
DATEDIFF(
minute,
S3.EndTime,
(SELECT MIN(StartTime)
FROM UserAgenda AS S4
WHERE S4.StartTime > S3.EndTime)), 15) <= 15),
EndTime) AS EndTime
FROM UserAgenda AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTime)
FROM UserAgenda AS S2
WHERE S2.EndTime < S1.StartTime),S1.StartTime),15)<= 15
<meetze@.gmail.com> wrote in message
news:1109184358.837536.30570@.f14g2000cwb.googlegroups.com...
> David - you are correct, thanks for the help.
> I have another question if you or anyone else it up for it.
> Using the same table schema as above, I have the following data:
> Start End
> 2005-01-01 08:15:00.000 2005-01-01 08:45:00.000
> 2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
> 2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
> 2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
> 2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
> 2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
> Now I want to place an item in from 8:30 to 8:45 and I want my end
> result to look like:
> Start End
> 2005-01-01 08:15:00.000 2005-01-01 08:30:00.000
> 2005-01-01 08:30:00.000 2005-01-01 08:45:00.000
> 2005-01-01 08:45:00.000 2005-01-01 09:00:00.000
> 2005-01-01 09:00:00.000 2005-01-01 10:00:00.000
> 2005-01-01 14:00:00.000 2005-01-01 15:00:00.000
> 2005-01-01 15:00:00.000 2005-01-01 16:00:00.000
> 2005-01-01 16:00:00.000 2005-01-01 17:00:00.000
> 2005-01-01 17:15:00.000 2005-01-01 17:30:00.000
> I had to insert the new time in a current time and split the other time
> to two parts and make it fit. Does make since?
> Thanks
> Richard Meetze
>