CODEWITHSUNDEEP
sql-server-2008datetime
AKV
AKV

Reputation: 179

How to compare 1 set of date with another set of date in SQL Server for overlapping period

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

Answers (1)

Richard Deeming
Richard Deeming

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

Related Questions