SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Overlapping dates trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

reamades
Starting Member

4 Posts

Posted - 07/18/2014 :  17:37:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/18/2014 :  18:48:00  Show Profile  Reply with Quote
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
			)
	)
	

Edited by - Lamprey on 07/18/2014 18:48:31
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 07/19/2014 :  08:09:50  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

reamades
Starting Member

4 Posts

Posted - 07/19/2014 :  14:39:39  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 07/20/2014 :  18:07:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

reamades
Starting Member

4 Posts

Posted - 07/21/2014 :  11:50:05  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30282 Posts

Posted - 07/21/2014 :  17:48:52  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Edited by - SwePeso on 07/21/2014 17:49:27
Go to Top of Page

reamades
Starting Member

4 Posts

Posted - 07/22/2014 :  11:41:34  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000