How to find overlapping time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to find overlapping time

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-11-10 : 06:32:00

DECLARE @Test AS TABLE (StartTime TIME, EndTime TIME)

INSERT INTO @Test VALUES ('07:00:00', '08:25:00')
INSERT INTO @Test VALUES ('09:00:00', '09:10:00')
INSERT INTO @Test VALUES ('10:00:00', '11:00:00')

SELECT * FROM @Test

DECLARE @StartTime TIME, @EndTime TIME

--Case 1 Non overlapping times

SET @StartTime = '11:00:00'
SET @EndTime = '11:10:00'

SET @StartTime = '06:00:00'
SET @EndTime = '07:00:00'

SET @StartTime = '08:25:00'
SET @EndTime = '08:55:00'

SET @StartTime = '09:10:00'
SET @EndTime = '09:30:00'

--If I give any of the above value it should insert
if not exists (select * from @Test where (@StartTime between StartTime and EndTime) or (@EndTime between StartTime and EndTime))
begin

INSERT INTO @Test VALUES (@StartTime, @EndTime)

end

--Case 2 Overlapping times

SET @StartTime = '06:00:00'
SET @EndTime = '07:10:00' -- It is already in the table so we should not insert ('07:00:00', '08:25:00')

SET @StartTime = '08:25:00'
SET @EndTime = '09:25:00' --It is already in the table so we should not insert

SET @StartTime = '09:05:00' --It is already in the table so we should not insert
SET @EndTime = '09:10:00'

--If I give any of the above value it should not insert since the "case 2" times overlap with the time in the table
if not exists (select * from @Test where (@StartTime between StartTime and EndTime) or (@EndTime between StartTime and EndTime))
begin
INSERT INTO @Test VALUES (@StartTime, @EndTime)
end

-- How to write the condition

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-11-10 : 12:29:36

TIME is not a valid SQL Server 2005 datatype.

Are you using SQL 2008, instead of SQL 2005?

CODO ERGO SUM

baburk
Posting Yak Master

108 Posts

Posted - 2009-11-10 : 21:29:05

quote:
Originally posted by Michael Valentine Jones

TIME is not a valid SQL Server 2005 datatype.

Are you using SQL 2008, instead of SQL 2005?

CODO ERGO SUM

I am using SQL SERVER 2008. I wrongly posted

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-10 : 22:39:48

[code]
insert into @Test
select @StartTime, @EndTime
where not exists
(
select *
from @Test x
where x.StartTime <= @StartTime and x.EndTime > @StartTime
)
and not exists
(
select *
from @Test x
where x.StartTime < @EndTime and x.EndTime >= @EndTime
)
and not exists
(
select *
from @Test x
where x.StartTime > @StartTime and x.EndTime < @EndTime
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources