Advanced Set-based Trigger (validating date ranges)?
-
I am attempting to write an advanced trigger to validate a date range in my table.
Table X:
- id1 INT NOT NULL
- id2 INT NOT NULL
- start SMALLDATETIME NOT NULL
- stop SMALLDATETIME NULL
*NOTE THAT "end" is nullable!
I want to make certain that all inserted and updated records do not allow overlapping dates for records with the same id1 and id2.
This is EASY with single records. However, I am trying to use set-based operations to handle bulk inserts/updates (without using CURSORs).
What I want is:
Insert/Update all records that 1) do not already exist with id1 & id2 or 2) do not have overlapping dates.
Given a trigger...
CREATE TRIGGER dbo.x_validate ON x INSTEAD OF INSERT,UPDATE AS
I can retrieve *invalid* sets using:
SELECT x.*
FROM inserted new
INNER JOIN x ON
new.id1 = x.id1 AND
new.id2 = x.id2 AND
(
new.start BETWEEN x.start AND x.stop OR
(
new.stop IS NOT NULL AND
new.stop BETWEEN x.start AND x.stop
)
)
However, I want all VALID sets.
Related, but off-topic: This table has additional constraints to ensure that id1 <> id2 and stop > start.
-
Eric, your current code misses an overlay... see line 8 below...
First of all, let's draw a picture of everything that has a start date less than the end date and the end date is not null. Line 01 represents the existing record... the other lines represent what a new record could have IF THERE'S A DATE RANGE OVERLAP...
01 S---------E (Existing record)
02 A---------B (possible new rec ranges from here down)
03 | A-----|---B
04 A---|-----B |
05 A---------B |
06 | A---------B
07 | A--B |
08 A---------|---------|---------B (Your code misses this one)
Now, let's figure this out... what simple conditions exist where we can easily identify that all the new record attempts in lines 02 through 08 have a date range (not working on the NULL end date thingy, yet... wait for it) that overlaps the existing record shown in line 01... You did better than most but, like I said, you missed the type of record in line 08. Here's the observation...
... All occurrences of "A" are ALWAYS <= "E"
and... All occurrences of "B" are ALWAYS >= "S"
and there are no exceptions. If the above conditions exist, then there must be a date range overlap between the new record and the old record.
A lot of people make the mistake of trying to find if the date range overlap exists when what you're really trying to find out is if the date range overlap doesn't exist...
With that in mind, this is true for those new records that don't have a date range overlap of the existing record...
01 S---------E (Existing record)
| |
09 A----B | |
10 | | A----B
... ANY occurrences of "A" are > "E"
OR ... ANY occurrences of "B" are < "S"
and there are no exceptions. If the above conditions exist, then there is NO date range overlap between the new record and the old record.
NOTICE that if "B" were to be a NULL on line 09, then it would cause the drawing to look like this...
01 S---------E (Existing record)
| |
09 A------------------------------------------>>>(null)
10 | | A----B
THAT'S kinda handy because a NULL can never equate to a value (unless you've made the horrible mistake of changing a server wide setting). So in the case of line 09 with NULL for "B", the condition of "B" < "S" would still work because "B" cannot be less than "S" if "B" is NULL because it can't be compared at all!
So, instead of you trying to find out if there is an overlap, take the easy road... try to find if there is NO overlap... so, in the same terms you wrote your good example in...
--===== Retrieve all "valid" rows that have no date range overlap with new recs
SELECT x.*
FROM Inserted new
INNER JOIN x
ON new.id1 = x.id1
AND new.id2 = x.id2
AND (
new.Start > x.Stop
OR
new.Stop < x.Start
)
Remember, the key is that because NULLS cannot be relationally compared, if new.Stop is NULL, the record will NOT qualify as "Valid".
{EDIT}
The same thing happens for if an end date is null in the existing records...
01 S------------------------->>>(null) (Existing record)
| |
09 A----B | |
10 | | A----B
One of the conditions is "A" > "E"... in this case, "E" is NULL and the comparison will not be able to be accomplished. This means the two records (original and line 10) have a date range overlap and will not be qualified in the SELECT I posted.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here was the result I came up with... Jeff: I attempted to follow your notes and appreciated the test case examples. However, I did have difficulty translating the notes into a solution. Perhaps you could review the solution below and make some suggestions?
CREATE TABLE dt
(
dt_id INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
start SMALLDATETIME NOT NULL,
stop SMALLDATETIME NULL
);
GOSET NOCOUNT ON;
INSERT INTO dt ( start, stop ) VALUES ( '1/1/2000', '12/31/2000 11:59' );
INSERT INTO dt ( start, stop ) VALUES ( '1/2/2001', '12/31/2001 11:59' );
INSERT INTO dt ( start, stop ) VALUES ( '1/1/2004', NULL ); --no end
GODECLARE @start SMALLDATETIME, @stop SMALLDATETIME;
DECLARE @last_stop SMALLDATETIME;--test params
SELECT @start = '1/1/2003', @stop = '1/2/2005';--max. smalldatetime value
SET @last_stop = CAST('June 6, 2079 11:59' AS SMALLDATETIME);--use max value if null
IF @stop IS NULL
SET @stop = @last_stop;IF EXISTS (
SELECT TOP 1 dt_id
FROM dt (fastfirstrow)
WHERE
--check if @start param is between start and stop dates
( ( @start > start ) AND ( @start < ISNULL(stop, @last_stop) ) ) OR
--check if @stop param is between start and stop dates
( ( @stop > start ) AND ( @stop < ISNULL(stop, @last_stop) ) ) OR
--check if start is between start and stop params
( ( start > @start ) AND ( start < @stop ) ) OR
--check if stop is between start and stop params
( ( ISNULL(stop, @last_stop) > @start ) AND ( ISNULL(stop, @last_stop) < @stop ) )
) PRINT 'yes';
ELSE PRINT 'no';
GODROP TABLE dt;
GO -
Dang... sorry Eric... got so tied up in the theory of it all, I forgot to demo how to do it set based...
Most of the SELECT list in the code below is just fluff to make the output pretty... the real key is in the JOIN and the WHERE clause. Contrary to what I said in my first post, I did go for the first option of finding where conflicts existed instead of where they do not.
I'm thinking that you may not want to use a trigger but you could with (what I think might be) simple mods to the code below. Sorry I can't spend a bit more time on this 'cause it's fun but I actually am getting ready for my once or twice a year, short term sabatical away from computers... I'm going on vacation sans any electronics. Just me, my fishing rod, and some cold beer!
Here's the full test setup and demonstration code of how to do it with many desired reservations against many actual reservations. The only shortcoming I see is that I don't check for conflicts against each other in the new batch of desired reservations... I did the tables as temp tables so I don't take a chance on dropping one of your permanent tables by mistake...
...and, yeah, I'm lazy so I took the easy way out on the date column formatting so that I could add words to a date column...
--===== Original problem definition posted by the user...
--"I want to make certain that all inserted and updated records do not allow
--overlapping dates for records with the same id1 and id2."
--===== If the experimental tables already exist, drop them
IF OBJECT_ID('TempDB..#Reserved') IS NOT NULL
DROP TABLE #Reserved
IF OBJECT_ID('TempDB..#Desired') IS NOT NULL
DROP TABLE #Desired
SET NOCOUNT ON
--===== This table simulates a table of known reserved dates by ID1/ID2
CREATE TABLE #Reserved
(
tmpReservedID INT IDENTITY(1, 1) PRIMARY KEY,
ID1 INT,
ID2 INT,
StartDate SMALLDATETIME NOT NULL,
StopDate SMALLDATETIME NULL
)
--===== This table simulates a table of desired reserved dates by ID1/ID2
CREATE TABLE #Desired
(
tmpDesiredID INT IDENTITY(1, 1) PRIMARY KEY,
ID1 INT,
ID2 INT,
StartDate SMALLDATETIME NOT NULL,
StopDate SMALLDATETIME NULL
)
--===== Populate the known reservation table with test data
INSERT INTO #Reserved (ID1,ID2,StartDate,StopDate)
SELECT 1,2,'1/1/2000', '12/31/2000 23:59' UNION ALL
SELECT 1,2,'1/2/2001', '12/31/2001 23:59' UNION ALL --1/1 is open
SELECT 1,2,'1/1/2004', NULL UNION ALL --No end
SELECT 3,4,'1/1/2000', '12/31/2000 23:59' UNION ALL
SELECT 3,4,'1/9/2001', '12/31/2001 23:59' UNION ALL --1/1 thru 1/8 are open
SELECT 3,4,'1/1/2004', '2/1/2004 23:59'
--===== Populate the desired reservation table with test data
INSERT INTO #Desired (ID1,ID2,StartDate,StopDate)
SELECT 1,2,'05/05/2000','05/05/2001' UNION ALL --Violates 2 existing reservations
SELECT 1,2,'04/04/2001','04/30/2001' UNION ALL --Violates 1 existing reservation
SELECT 1,2,'01/01/2005','01/01/2006' UNION ALL --Violates 1 existing reservation (tickles the null)
SELECT 1,2,'01/01/2001','01/01/2001 23:59' UNION ALL --Fits into the 1 day slot available
SELECT 1,2,'01/01/1999','12/31/1999 23:59' UNION ALL --Fits in before all the other reservations
SELECT 3,4,'02/02/2004',NULL UNION ALL --No conflict for extended stay
SELECT 3,4,'02/01/2004',NULL UNION ALL --Conflict by 1 day for extended stay
SELECT 3,4,'01/03/2001','01/08/2001 23:59' --Fits into 8 day slot with room at beginning
--===== Create a sample report to demo conflicting and non-conflicting dates
SELECT d.ID1,d.ID2,
DesiredStartDate = CONVERT(VARCHAR(30),d.StartDate,100),
DesiredStopDate = CASE
WHEN d.StopDate IS NULL
THEN 'Extended...'
ELSE CONVERT(VARCHAR(30),d.StopDate,100)
END,
Status = CASE
WHEN r.StartDate IS NULL
THEN 'No Conflict. OK to Book'
ELSE 'Conflict. DO NOT BOOK'
END,
ConflictingStartDate = ISNULL(CONVERT(VARCHAR(30),r.StartDate,100),''),
ConflictingStopDate = CASE
WHEN r.StartDate IS NOT NULL
AND r.StopDate IS NULL
THEN 'Extended...'
ELSE ISNULL(CONVERT(VARCHAR(30),r.StopDate,100),'')
END
FROM #Desired d
LEFT OUTER JOIN
#Reserved r
ON d.ID1 = r.ID1
AND d.ID2 = r.ID2
AND (d.StartDate <= r.StopDate OR r.StopDate IS NULL)
AND (d.StopDate >= r.StartDate OR d.StopDate IS NULL)
--===== Basic algorithm used from previous post
-- ... All occurrences of "A" are ALWAYS <= "E"
-- and... All occurrences of "B" are ALWAYS >= "S"
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s. Almost forgot... (pardon the soapbox, please) if you get into the habit of writing setbased code that will handle more than 1 row at a time, you've also solved the problem of handling just one row at a time in a set based fashion (that's a hint).
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply