Date/Time overlaps - urgent.

[axion_sa]

Hi,

What I have is a booking table, and when updating/inserting I need to
ensure that there are no date/time overlaps. The problem I'm having is
that while the following script works for events on the same day, it
fails miserably when a booking starts on a previous day.

I've just spent the last hour going through previous posts and just
can't seem to it right.

My DB structure (Sql Server 2000):

Table: CollateralBooki ng

-- CBID - int, identity(1, 1)

-- CBcPartNumber - varchar(50) (foreign key)

-- CBdDateTimeFrom - smalldatetime

-- CBdDateTimeTo - smalldatetime

-- CBcAlias - varchar(50) (foreign key)

My current script (in a stored proc):
IF (SELECT COUNT(*) FROM CollateralBooki ng
WHERE (((@CBdDateTime From > CBdDateTimeFrom ) AND (@CBdDateTimeFr om < CBdDateTimeTo))
OR ((@CBdDateTimeT o > CBdDateTimeFrom ) AND (@CBdDateTimeTo < CBdDateTimeTo)) )
AND (CBcPartNumber = @CBcPartNumber) ) <> 0
BEGIN
-- Return an error.
END

-- ... Other checks & finally, the insert/update.
--
Posted via http://dbforums.com

[David Portas]

First, add a constraint, if you haven't already, to ensure that the "from"
datetime is less than the "to" datetime (I've guessed your primary key and
only included the essential columns)

CREATE TABLE CollateralBooki ng (cbid INTEGER UNIQUE, cbcpartnumber INTEGER,
cbddatetimefrom DATETIME NOT NULL, cbddatetimeto DATETIME NOT NULL, CHECK
(cbddatetimefro m<cbddatetimeto ), PRIMARY KEY (cbcpartnumber,
cbddatetimefrom ))

IF EXISTS
(SELECT *
FROM CollateralBooki ng AS A
JOIN CollateralBooki ng AS B
ON A.cbcpartnumber =B.cbcpartnumbe r AND A.cbid<>B.cbid
AND NOT (A.cbddatetimef rom > B.cbddatetimeto
OR A.cbddatetimeto < B.cbddatetimefr om))
/* Raise an error */

--
David Portas
------------
Please reply only to the newsgroup
--

[axion_sa]

Thanks David :) It's working well now.

On a side note, CBID was the primary key.
--
Posted via http://dbforums.com