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
)
)
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -