Overlapping dates trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Overlapping dates trigger

Author

Topic

reamades
Starting Member

5 Posts

Posted - 2014-07-18 : 17:37:44

Hello all, I need some help with a trigger to prevent overlapping dates. I feel so close but I'm not sure what I'm missing. The details;

Rates table holds all day rates for contractors per project. So I need to prevent any date overlap for the same contractor for the same project.

Columns: RateID(primary key), AgentID (contractor), ProjectID, StartDate, EndDate

Below is the trigger I am trying but prevents any new records from being created;

ALTER TRIGGER [dbo].[t_RateOverlap]
ON [dbo].[Rates]
AFTER INSERT, UPDATE
AS
IF EXISTS(
SELECT * FROM Rates inner join inserted on (Rates.AgentID=inserted.AgentID AND Rates.ProjectID=inserted.ProjectID AND inserted.StartDate BETWEEN Rates.StartDate AND Rates.EndDate) OR
(Rates.AgentID=inserted.AgentID AND Rates.ProjectID=inserted.ProjectID AND inserted.EndDate BETWEEN Rates.StartDate AND Rates.EndDate)
)
BEGIN
RAISERROR ('Error: Rates cannot overlap.', 16, 1)
ROLLBACK TRANSACTION
END
GO

If anyone has suggestions they would be greatly appreciated, thanks.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-18 : 18:48:00

I think either of these will work. If you want to post sample data that we can run queries against, then you might get some tested code. Until then:


SELECT 
	* 
FROM 
	Rates
INNER JOIN
	inserted T 
	ON inserted.AgentID = Rates.AgentID 
	AND inserted.ProjectID = Rates.ProjectID
	AND inserted.ID <> Rates.ID
WHERE
	Rates.StartDate BETWEEN inserted.StartDate AND inserted.EndDate
	OR Rates.EndDate BETWEEN inserted.StartDate AND inserted.EndDate


-- Or

SELECT 
	* 
FROM 
	Rates 
WHERE 
	EXISTS
	(
		SELECT 
			1 
		FROM 
			Inserted
		WHERE 
			Inserted.AgentID = Rates.AgentID 
			AND Inserted.ProjectID = Rates.ProjectID
			AND Inserted.ID <> Rates.ID
			AND 
			(
				Rates.StartDate BETWEEN Inserted.StartDate AND Inserted.EndDate
				OR Rates.EndDate BETWEEN Inserted.StartDate AND Inserted.EndDate
			)
	)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-19 : 08:09:50

[code]SELECT *
FROM dbo.Rates AS r
INNER JOIN inserted AS i ON i.AgentID = r.AgentID
AND i.ProjectID = r.ProjectID
AND i.ID <> r.ID
WHERE r.StartDate <= i.EndDate
AND r.EndDate >= i.StartDate[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

reamades
Starting Member

5 Posts

Posted - 2014-07-19 : 14:39:39

quote:

Originally posted by Lamprey

I think either of these will work. If you want to post sample data that we can run queries against, then you might get some tested code. Until then:


SELECT 
	* 
FROM 
	Rates
INNER JOIN
	inserted T 
	ON inserted.AgentID = Rates.AgentID 
	AND inserted.ProjectID = Rates.ProjectID
	AND inserted.ID <> Rates.ID
WHERE
	Rates.StartDate BETWEEN inserted.StartDate AND inserted.EndDate
	OR Rates.EndDate BETWEEN inserted.StartDate AND inserted.EndDate


-- Or

SELECT 
	* 
FROM 
	Rates 
WHERE 
	EXISTS
	(
		SELECT 
			1 
		FROM 
			Inserted
		WHERE 
			Inserted.AgentID = Rates.AgentID 
			AND Inserted.ProjectID = Rates.ProjectID
			AND Inserted.ID <> Rates.ID
			AND 
			(
				Rates.StartDate BETWEEN Inserted.StartDate AND Inserted.EndDate
				OR Rates.EndDate BETWEEN Inserted.StartDate AND Inserted.EndDate
			)
	)

The first code worked perfectly. Thank you very much

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-20 : 18:07:21

No, it will only work for partially overlapping rates.
What about completely overlapping intervals such as 1/1/2014 to 12/31/2014 with 6/1/2014 to 6/30/2014?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

reamades
Starting Member

5 Posts

Posted - 2014-07-21 : 11:50:05

quote:
Originally posted by SwePeso

No, it will only work for partially overlapping rates.
What about completely overlapping intervals such as 1/1/2014 to 12/31/2014 with 6/1/2014 to 6/30/2014?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

That's a good point, but this;

WHERE r.StartDate <= i.EndDate
AND r.EndDate >= i.StartDate

will only find a match on dates that completely overlap and not partial. I suppose the following will catch any and all overlapping dates;

WHERE
Rates.StartDate BETWEEN inserted.StartDate AND inserted.EndDate
OR Rates.EndDate BETWEEN inserted.StartDate AND inserted.EndDate
OR inserted.StartDate BETWEEN Rates.StartDate AND Rates.EndDate
OR inserted.EndDate BETWEEN Rates.StartDate AND Rates.EndDate

If anyone sees a simpler solution please let me know. But as far as I can tell this code should do the job as needed. Thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-21 : 17:48:52

No. Try at least once and you will see it will work. This part

WHERE	 r.StartDate <= i.EndDate
	AND r.EndDate >= i.StartDate

will find both partially overlapping intervals and fully overlapping intervals.
It's a pity you waste your time (and mine) dismissing something I have used for years.

DECLARE	@Sample TABLE
	(
		RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		FromDate DATE NOT NULL,
		ToDate DATE NOT NULL
	);

INSERT	@Sample
	(
		FromDate,
		ToDate
	)
VALUES	('20140101', '20140630'),
	('20140701', '20140930'),
	('20140201', '20140228'),
	('20140615', '20140715'),
	('20141001', '20141231');

-- SwePeso
SELECT		*
FROM		@Sample AS s
INNER JOIN	@Sample AS q ON q.RowID > s.RowID
WHERE		s.FromDate <= q.ToDate
		AND s.ToDate >= q.FromDate;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

reamades
Starting Member

5 Posts

Posted - 2014-07-22 : 11:41:34

quote:
Originally posted by SwePeso

No. Try at least once and you will see it will work. This part
WHERE r.StartDate <= i.EndDate
AND r.EndDate >= i.StartDate
will find both partially overlapping intervals and fully overlapping intervals.
It's a pity you waste your time (and mine) dismissing something I have used for years.
DECLARE @Sample TABLE
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
FromDate DATE NOT NULL,
ToDate DATE NOT NULL
);

INSERT @Sample
(
FromDate,
ToDate
)
VALUES ('20140101', '20140630'),
('20140701', '20140930'),
('20140201', '20140228'),
('20140615', '20140715'),
('20141001', '20141231');

-- SwePeso
SELECT *
FROM @Sample AS s
INNER JOIN @Sample AS q ON q.RowID > s.RowID
WHERE s.FromDate <= q.ToDate
AND s.ToDate >= q.FromDate;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

You're right, this does work and seems to be the most efficient check. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources