November 25, 2005 at 7:43 am
Feeling a bit off today.
Have an interesting question, which I think should have a standard answer.
eg:
create table x
(
validation char(10) not null,
validFrom datetime not null,
validTo datetime not null
)
Is there any kind of table level constraint etc that I can put on to make sure there are no overlapping 'validations'.
eg
insert into x values 'valid1', '20050101', '20050201'
insert into x values 'valid1', '20050201', '20050301'
would be ok but then
insert into x values 'valid1', '20050111', '20050115'
would fail?
November 25, 2005 at 12:23 pm
There are several way to do this, here goes one:
create table x
(
validation char(10) not null,
validFrom datetime not null,
validTo datetime not null
)
create function dbo.IsRangeValid ( @s-2 datetime, @e datetime)
returns int
as
begin
declare @local int
if exists( select * from x
where (@s > validFrom and @s-2 < ValidTo) or
(@e > validFrom and @e < ValidTo)
set @local = 0
else
set @local = 1
return @local
end
go
alter table x
add constraint Check_for_overlap check( dbo.IsRangeValid(validFrom ,validTo) = 1 )
go
Cheers,
* Noel
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply