sql server (alternate):
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: CollateralBooking

-- 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 CollateralBooking
WHERE (((@CBdDateTimeFrom > CBdDateTimeFrom) AND (@CBdDateTimeFrom < CBdDateTimeTo))
OR ((@CBdDateTimeTo > CBdDateTimeFrom) AND (@CBdDateTimeTo < CBdDateTimeTo)))
AND (CBcPartNumber = @CBcPartNumber)) <> 0
BEGIN
-- Return an error.
END

-- ... Other checks & finally, the insert/update.


--

Thanks David :) It's working well now.



On a side note, CBID was the primary key.


--

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 CollateralBooking (cbid INTEGER UNIQUE, cbcpartnumber INTEGER,
cbddatetimefrom DATETIME NOT NULL, cbddatetimeto DATETIME NOT NULL, CHECK
(cbddatetimefrom<cbddatetimeto), PRIMARY KEY (cbcpartnumber,
cbddatetimefrom))

IF EXISTS
(SELECT *
FROM CollateralBooking AS A
JOIN CollateralBooking AS B
ON A.cbcpartnumber=B.cbcpartnumber AND A.cbid<>B.cbid
AND NOT (A.cbddatetimefrom > B.cbddatetimeto
OR A.cbddatetimeto < B.cbddatetimefrom))
/* Raise an error */

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