Constraint on DateFrom - DateTo...

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

Transact-SQL (2000)

Constraint on DateFrom - DateTo...

Author

Topic

florinlabou
Starting Member

3 Posts

Posted - 2004-09-18 : 13:00:20

Hi,

I need some help with a constraint. I have a table with the following structure (partial):

myTable:
TableID bigint (IDENTITY),
DateFrom datetime,
DateTo datetime

Basically, I need to put a constraint, so the newly created record should not overlap as period (read here: DateFrom - DateTo) another record.

I'm looking for an efficient way to verify the overlapping of two periods.

Any help will be greatly appreciated. Thanks.

Cheers,
Florin

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-18 : 17:02:35

I might be tempted to do this in a trigger

create trigger tr on tbl for insert, update
as
if update(datefrom) or update(dateto)
if exists
(select *
from tbl t, inserted i
where i.datefrom < t.dateto
and i.dateto > t.datefrom
and t.pk <> i.pk
)
begin
raiserror('overlapping date range', 16, -1)
rollback trn
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

florinlabou
Starting Member

3 Posts

Posted - 2004-09-19 : 04:41:36

quote:
Originally posted by nr

I might be tempted to do this in a trigger

create trigger tr on tbl for insert, update
as
if update(datefrom) or update(dateto)
if exists
(select *
from tbl t, inserted i
where i.datefrom < t.dateto
and i.dateto > t.datefrom
and t.pk <> i.pk
)
begin
raiserror('overlapping date range', 16, -1)
rollback trn
end

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr, thanks for your reaction. I was thinking to the trigger as well.
By 'constraint' I didn't really meant a table contraint. :)

Maybe I missed some details. Here is the real problem:
(Sorry, for the first explanation, it was a bit vague)

Here is the problem:

i = record inserted
t = existing record
_ = spacer

NOT ALLOWED:

______i.DateFrom_______________i.DateTo_______
---------|------------------------|-----------

__t.DateFrom_____t.DateTo_____________________ (1)
----|--------------|--------------------------

___________t.DateFrom______t.DateTo___________ (2)
--------------|--------------|----------------

______________________t.DateFrom______t.DateTo (3)
-------------------------|--------------|------

__t.DateFrom__________________________t.DateTo (4)
----|-----------------------------------|------

ALLOWED:

t.DateFrom_t.DateTo___i.DateFrom________i.DateTo
---|--------|-----------|-----------------|-----------

i.DateFrom_i.DateTo___t.DateFrom________t.DateTo
---|--------|-----------|-----------------|-----------

Simple Allowed Expression:

t.DateTo < i.DateFrom OR
t.DateFrom > i.DateTo

That means:

Not Allowed should be:

NOT (<Simple Allowed Expression>)

Well, the question remains open. How should I do this in an efficient way?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-09-19 : 10:03:14

You will need to join on the PK to inserted in the trigger and it is always nicer not to mix and's and or's - see the condition in the trigger I gave - which is the same as your's except the not is included in the test.
not (t.DateTo < i.DateFrom OR t.DateFrom > i.DateTo)
not t.DateTo < i.DateFrom AND not t.DateFrom > i.DateTo)
t.DateTo >= i.DateFrom AND t.DateFrom <= i.DateTo
are the same

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

florinlabou
Starting Member

3 Posts

Posted - 2004-09-19 : 10:37:25

quote:
Originally posted by nr

You will need to join on the PK to inserted in the trigger and it is always nicer not to mix and's and or's - see the condition in the trigger I gave - which is the same as your's except the not is included in the test.
not (t.DateTo < i.DateFrom OR t.DateFrom > i.DateTo)
not t.DateTo < i.DateFrom AND not t.DateFrom > i.DateTo)
t.DateTo >= i.DateFrom AND t.DateFrom <= i.DateTo
are the same

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr thanks a lot!

You are right, the condition is the same, I could see that if I would check my 'drawing' against your condition.

It seams that I should have a break and a cold beer

It's Sunday anyway... but 'addiction' doesn't rest...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources