Preventing overlapping date records

Hi,
I have a SQL Server 2000 table which holds timesheet data. Each timesheet is
made up of one or more timesheet elements which would e.g. correspond to the
number of hours worked by an employee in any given day. I'm looking for a
way to ensure that employees do not enter duplicate or overlapping records.
E.g. let's say tblTimesheet holds the following records:
dtmStart dtmEnd
-- --
2003-09-15 09:15:00 2003-09-15 17:00:00
2003-09-16 09:15:00 2003-09-16 17:00:00
I need some help with a stored procedure which will accept a starting
datetime and an ending datetime as parameters and tell me whether that would
overlap in any way with any existing record. The two parameter dates will
already have been validated before being passed to the SP, so I know that
they are valid dates and that the ending datetime will be later than the
starting date time.
So,
CREATE PROCEDURE usp_ValidateTimesheetEntry
@.pdtmStart datetime,
@.pdtmEnd datetime
SELECT COUNT(*) FROM tblTimesheet
WHERE ...-- this is the bit I'm stuck on
Examples
=======
If @.pdtmStart is 2003-09-17 09:15:00 and @.pdtmEnd is 2003-09-17 17:00:00
this is valid.
If @.pdtmStart is 2003-09-16 18:30:00 and @.pdtmEnd is 2003-09-16 23:50:00
this is valid.
If @.pdtmStart is 2003-09-15 01:00:00 and @.pdtmEnd is 2003-09-15 09:30:00
this is invalid because it overlaps with the first record.
If @.pdtmStart is 2003-09-16 11:00:00 and @.pdtmEnd is 2003-09-16 16:30:00
this is invalid because it overlaps (or rather is totally contained within)
the second record.
Any assistance gratefully received.
MarkYou can use a combination of constraints and a trigger:
CREATE TABLE Timesheet (employee INTEGER NOT NULL /* REFERENCES
Employees (employee) */, dtmstart DATETIME NOT NULL, dtmend DATETIME
NOT NULL, CHECK (dtmstart<dtmend), UNIQUE (employee,dtmstart))
GO
CREATE TRIGGER trg_timesheet_no_overlap
ON Timesheet
FOR INSERT, UPDATE
AS
IF EXISTS
(SELECT * FROM inserted I
JOIN Timesheet T ON
I.employee = T.employee
AND I.dtmend > T.dtmstart
AND I.dtmstart < T.dtmend
AND I.dtmstart <> T.dtmstart)
BEGIN
RAISERROR ('Overlapping times not allowed', 16, 1)
ROLLBACK TRANSACTION
END
Notice that this will allow double shifts, i.e. 1st Shift End = 2nd
Shift Start. If you don't want that just change > and < to >= and <=.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110719703.701430.301990@.f14g2000cwb.googlegroups.com...

> You can use a combination of constraints and a trigger:
Perfect! Thanks very much.