January 22, 2011 at 5:49 pm
I have a table with date ranges. I need a way to reject conflicting rows. How can I reject overlapping dates. Is there a way to handle this with a unique index, or would I need to resort to a trigger?
--* Here's the table
DECLARE @Schedule TABLE(StartDateTime DateTime, EndDateTime DateTime, Slots int);
--* A few valid rows
INSERT INTO @Schedule(StartDateTime, EndDateTime, Slots)
VALUES('3/1/2011 07:00:00 AM', '3/1/2011 06:00:00 PM', 10);
INSERT INTO @Schedule(StartDateTime, EndDateTime, Slots)
VALUES('3/2/2011 07:00:00 AM', '3/2/2011 06:00:00 PM', 10);
INSERT INTO @Schedule(StartDateTime, EndDateTime, Slots)
VALUES('3/3/2011 07:00:00 AM', '3/3/2011 06:00:00 PM', 8);
--* A few conflicting rows. How can I reject them?
INSERT INTO @Schedule(StartDateTime, EndDateTime, Slots)
VALUES('3/3/2011 10:00:00 AM', '3/3/2011 11:30:00 AM', 8);
INSERT INTO @Schedule(StartDateTime, EndDateTime, Slots)
VALUES('2/28/2011 11:00:00 PM', '3/1/2011 08:00:00 AM', 5);
INSERT INTO @Schedule(StartDateTime, EndDateTime, Slots)
VALUES('3/3/2011 05:00:00 PM', '3/3/2011 11:30:00 PM', 8);
INSERT INTO @Schedule(StartDateTime, EndDateTime, Slots)
VALUES('2/26/2011 10:00:00 AM', '3/5/2011 11:30:00 AM', 40);
.
January 22, 2011 at 6:26 pm
you can use a unique index to prevent dupes from being entered into your table. avoid triggers since this will cause a performance hit if you have a lot of inserts.
-----------------------------
www.cbtr.net
.: SQL Backup Admin Tool[/url] :.
January 22, 2011 at 6:45 pm
I don't know a good way - you could do it with a trigger but that's not nice...
Have you considered storing the data differently, for example having a table of Slots and assigning a reference to them when they are in use?
Something like
CREATE TABLE Slots(StartDate DateTime not null PRIMARY KEY ,Length int, assigned int NULL);
INSERT Slots(StartDate,Length) VALUES('2011-02-03 07:00:00',60)
INSERT Slots(StartDate,Length) VALUES('2011-02-03 08:00:00',60)
INSERT Slots(StartDate,Length) VALUES('2011-02-03 09:00:00',60)
INSERT Slots(StartDate,Length) VALUES('2011-02-03 10:00:00',60)
INSERT Slots(StartDate,Length) VALUES('2011-02-03 11:00:00',60)
--etc...
--then when scheduling a job from 7am on 3rd Feb 2011 you just assign your job id to the required Slots.
declare @MyJobId int = 2
UPDATE Slots SET assigned=@MyJobID WHERE StartDate = '2011-02-03 07:00:00'
UPDATE Slots SET assigned=@MyJobID WHERE StartDate = '2011-02-03 08:00:00'
UPDATE Slots SET assigned=@MyJobID WHERE StartDate = '2011-02-03 09:00:00'
This way you can never have an overlap.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 22, 2011 at 8:26 pm
Yes, I wish changing the table was an option, but it's just not right now. I'm stuck with it. :hehe:
.
January 23, 2011 at 2:22 am
... In which case, I guess you need an INSERT trigger that checks whether the current StartDate or EndData overlap with an existing one...
something like...
CREATE TRIGGER PreventOverlap
AFTER INSERT, UPDATE
AS
IF EXISTS(
SELECT 1
FROM inserted AS ins
WHERE EXISTS (
SELECT 1
FROM Schedule AS sch
WHERE ( sch.StartDate <= ins.StartDate AND sch.EndDate >= ins.StartDate )
OR ( ins.StartDate <= Sch.StartDate AND ins.EndDate >= sch.StartDate )
)
)
RAISERROR(N'Your error message',16,1);
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);January 23, 2011 at 5:13 am
Are slots involved in the calculation for overlaps? IE: Do you always have 20 slots available and need to make sure that you don't use more than 20 at any specific time, or is slots not part of this equation?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 23, 2011 at 9:52 am
Knowing that you stated you could not change the table(s), but in this examle I did, adding an Identity field (Row) to keep from going nuts when reviewing the results. Plus I added to your data (Conflicting entries) a NON conflicinting entry for test purposed - to be sure that it was not listed as conflicting. Next I hijacked (borrowed/copied) the WHERE clause from Mister.Magoo's trigger code.
To keep from re-doing and re-doing everything when developing and testing I elected to use your Table variable (Schedule) as a permanent table, and place your Conflicting entries into a TEMP TABLE. Next I assumed that you could place your existing data into a second temp table since some how or other you managed to place it into a Table variable. O.K. with all those caveats here is a possible solution.
CREATE TABLE #Ins([StartDateTime] [datetime] NULL,[EndDateTime] [datetime] NULL,[Slots] [int] NULL,[Row] [int] IDENTITY(1,1) NOT NULL)
--* A few conflicting rows. How can I reject them?
INSERT INTO #Ins(StartDateTime, EndDateTime, Slots)
VALUES('3/3/2011 10:00:00 AM', '3/3/2011 11:30:00 AM', 8);
INSERT INTO #Ins(StartDateTime, EndDateTime, Slots)
VALUES('2/28/2011 11:00:00 PM', '3/1/2011 08:00:00 AM', 5);
INSERT INTO #Ins(StartDateTime, EndDateTime, Slots)
VALUES('3/3/2011 05:00:00 PM', '3/3/2011 11:30:00 PM', 8);
INSERT INTO #Ins(StartDateTime, EndDateTime, Slots)
VALUES('2/26/2011 10:00:00 AM', '3/5/2011 11:30:00 AM', 40);
--- A none conflicting row - should NOT appear in the results
INSERT INTO #Ins(StartDateTime, EndDateTime, Slots)
VALUES('2011-03-01 19:00:00.000','2011-03-01 20:00:00.000',1)
SELECT Schedule.StartDateTime,Schedule.EndDateTime,Schedule.Row,
#Ins.StartDateTime AS '#Ins.StartDateTime',#Ins.EndDateTime AS '#Ins.EndDateTime',#Ins.Row
FROM Schedule
JOIN #ins ON (schedule.StartDatetime <= #ins.StartDatetime AND schedule.EndDatetime >= #ins.StartDatetime)
OR (#ins.StartDatetime <= Schedule.StartDatetime AND #ins.EndDatetime >= schedule.StartDatetime )
The results:
StartDateTime EndDateTime Row#Ins.StartDateTime#Ins.EndDateTimeRow
2011-03-01 07:00:00.0002011-03-01 18:00:00.00012011-02-28 23:00:00.0002011-03-01 08:00:00.0002
2011-03-01 07:00:00.0002011-03-01 18:00:00.00012011-02-26 10:00:00.0002011-03-05 11:30:00.0004
2011-03-02 07:00:00.0002011-03-02 18:00:00.00022011-02-26 10:00:00.0002011-03-05 11:30:00.0004
2011-03-03 07:00:00.0002011-03-03 18:00:00.00032011-03-03 10:00:00.0002011-03-03 11:30:00.0001
2011-03-03 07:00:00.0002011-03-03 18:00:00.00032011-03-03 17:00:00.0002011-03-03 23:30:00.0003
2011-03-03 07:00:00.0002011-03-03 18:00:00.00032011-02-26 10:00:00.0002011-03-05 11:30:00.0004
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply