No overlap in period - 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 2000 Forums

SQL Server Development (2000)

No overlap in period

Author

Topic

tjibbe
Starting Member

4 Posts

Posted - 2004-02-27 : 07:32:58

Can you create a table in SQL for by example reservations.

Where the it is not possible that two periodes overlap each other.

This with CONSTRAINS, CHECKS or other SQL commandos

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-02-27 : 08:24:11

quote:
SQL commandos

Could we call them....

Navy SQueaLs????

Sorry, just HAD to do it.

tjibbe
Starting Member

4 Posts

Posted - 2004-02-27 : 09:47:34

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-27 : 22:40:55

[code]create table reservation
(
rid int identity,
startdate datetime not null,
enddate datetime not null,
check ( startdate < enddate )
)
GO

-- constraint UDF
create function dbo.IsAvailable
(
@startdate datetime,
@enddate datetime
)
returns bit
as
begin
declare @bit bit
select @bit = isnull(min(case when (@startdate >= f or @enddate <= f) then 1 else 0 end),1)
from
(
select f
from
(
select top 100 percent f
from
(
select startdate f
from reservation
union
select enddate
from reservation
) d
order by f
) d
where f between @startdate and @enddate
group by f
) d

return @bit
end
GO

-- add constraint
alter table reservation add constraint ck_IsAvailable check (dbo.IsAvailable(startdate,enddate)=1)
GO

-- insert some sample data
insert into reservation
select '1/1/2004','1/2/2004' union all
select '1/2/2004','1/3/2004' union all
select '1/7/2004','1/10/2004'
GO

-- Pass
insert into reservation
select '1/5/2004','1/7/2004'

--Fails
insert into reservation
select '1/1/2004','1/7/2004'

drop table reservation
drop function dbo.IsAvailable[/code]

tjibbe
Starting Member

4 Posts

Posted - 2004-02-28 : 17:41:45

Thanx a lott

, I will try your solution with our postgreSQL database.

Tjibbe

tjibbe
Starting Member

4 Posts

Posted - 2004-03-02 : 06:30:04

Hey ehorn,

I have found de option CREATE FUNCTION in postgresql

But i can't get it work.

which Language you used?

C, internal, SQL or plpgsql?

u4cast
Starting Member

16 Posts