Reputation: 179
I have two sets of dates. I need to check whether the set A dates overlap on Set B dates in SQL Server
What is the best approach?.
Set A: (MM/DD/YYYY)
Date from: 1/1/2013
Date To: 2/15/2013
Set B (MM/DD/YYYY)
Date From : 2/10/2013
Date To : 2/20/2013
UPDATE
It's a leave application, where if an employee applies leave, I need to check whether the from and to date over laps on any other pending/Approved leave for the same employee. before letting the employee apply his leave.
Upvotes: 0
Views: 150
Reputation: 31248
Given two date ranges (A
and B
) you can test whether they overlap by checking if A
starts before B
finishes and A
finishes after B
starts. Depending on your data, it should be fairly simple to do this in SQL:
If Exists
(
SELECT 1
FROM LeaveTable
WHERE State In ('Pending', 'Approved')
And @ProposedStart < EndDate
And @ProposedEnd > StartDate
)
BEGIN
RAISERROR('The proposed leave overlaps.', 16, 1);
Return;
END;
Upvotes: 2