Need to check dates are overlapping or not in sql server

Question

Below is my input code

ID  EMP_ID       PROJECT_NAME            START_DATE          END_DATE
1   10016351    ABC                       22-12-2016        15-05-2017
2   10016351    ABC                       01-09-2016        22-11-2016
1   10081503    RTBS AMaaS                21-11-2016        15-02-2017
2   10081503    RTBS AMaaS                18-07-2016        25-11-2016
3   10081503    RTBS AMaaS                21-08-2016        13-10-2016
4   10081503    RTBS AMaaS                03-02-2015        22-05-2015
1   10089293    RTBS PDaaS                17-02-2017        31-12-2017
2   10089293    RTBS PDaaS                13-06-2016        14-02-2017

I need to check for an employee, dates are overlapping or not. If yes then it will return min Start date and Max end date and flag as 'O'.

For ex, for emp_id = 10081503 dates are overlapping 21-11-2016 is falling between 18-07-2016 and 25-11-2016, also 21-08-2016 is falling between 18-07-2016 and 25-11-2016 so the query should return start date as 18-07-2016 (min) and end date as 15-02-2017 (max) for the dates which are overlapping. For rest entries, it should return the same entry with flag 'N'.

EMP_ID | PROJECT_NAME | START_DATE | END_DATE | Flag 10081503 | RTBS AMaaS | 21-11-2016 | 15-02-2017 | O 10081503 | RTBS AMaaS | 03-02-2015 | 22-05-2015 | N

Like wise my final output should be,

ID  EMP_ID       PROJECT_NAME            START_DATE          END_DATE   FLAG
1   10016351    ABC                       22-12-2016        15-05-2017   N
2   10016351    ABC                       01-09-2016        22-11-2016   N
1    10081503   RTBS AMaaS                21-11-2016        15-02-2017   O
2    10081503   RTBS AMaaS                03-02-2015        22-05-2015   N
1   10089293    RTBS PDaaS                17-02-2017        31-12-2017   N
2   10089293    RTBS PDaaS                13-06-2016        14-02-2017   N

Answer 1

Using outer apply() to return the first id for overlaps and grouping by that value. Using row_number() to renumber the id:

select
    id = row_number() over (
      partition by t.emp_id 
      order by min(start_date) desc
      )
  , t.emp_id
  , t.project_name
  , start_date = min(start_date)
  , end_date   = max(end_date)
  , flag = max(case when x.id <> t.id then 'O' else 'N' end)
from t
  outer apply (
    select top 1 i.id
    from t i
    where i.Emp_Id = t.Emp_id
      and i.End_Date > t.Start_Date
      and t.End_Date > i.Start_Date
    order by i.Start_Date
      ) x
group by t.emp_id, t.project_name, x.id

rextester demo: http://rextester.com/VHMZ91714

returns:

+----+----------+--------------+------------+------------+------+
| id |  emp_id  | project_name | start_date |  end_date  | flag |
+----+----------+--------------+------------+------------+------+
|  1 | 10016351 | ABC          | 2016-12-22 | 2017-05-15 | N    |
|  2 | 10016351 | ABC          | 2016-09-01 | 2016-11-22 | N    |
|  1 | 10081503 | RTBS AMaaS   | 2016-07-18 | 2017-02-15 | O    |
|  2 | 10081503 | RTBS AMaaS   | 2015-02-03 | 2015-05-22 | N    |
|  1 | 10089293 | RTBS PDaaS   | 2017-02-17 | 2017-12-31 | N    |
|  2 | 10089293 | RTBS PDaaS   | 2016-06-13 | 2017-02-14 | N    |
+----+----------+--------------+------------+------------+------+

---

The above will collapse ranges that overlap each other, but if you need to collapse multiple ranges that overlap only at the edges into a single range we can use calendar or dates table like so:

For only 152kb in memory, you can have 30 years of dates in a table with this:

/* dates table */
declare @fromdate date = '20000101';
declare @years    int  = 30;
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
    [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
into dbo.Dates
from n as deka cross join n as hecto cross join n as kilo
               cross join n as tenK  cross join n as hundredK
order by [Date];
create unique clustered index ix_dbo_Dates_date on dbo.Dates([Date]);

If you do not want to create a dates table, you can use this query to generate the dates like so:

declare @fromdate date = '20100101';
declare @thrudate date = getdate();
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo
                cross join n as tenK cross join n as hundredK
   order by [Date]
)
, cte as (
  select
      t.emp_id
    , t.project_name
    , d.date
    , rn = row_number() over (partition by t.emp_id, t.project_name order by d.date)-1
    , flag = case when count(*) > 1 then 'O' else 'N' end  
  from t
    inner join dates d
      on d.date >= t.start_date
     and d.date <= t.end_date
  group by t.emp_id, t.project_name, d.date
)
select 
    id = row_number() over (partition by emp_id order by min(date) desc)
  , emp_id
  , project_name
  , start_date = min(date)
  , end_date = max(date)
  , flag = max(flag)
from cte
group by emp_id, project_name, dateadd(day,-rn,date)

rextester demo: http://rextester.com/QKEMH32326

returns:

+----+----------+--------------+------------+------------+------+
| id |  emp_id  | project_name | start_date |  end_date  | flag |
+----+----------+--------------+------------+------------+------+
|  1 | 10016351 | ABC          | 2016-12-22 | 2017-05-15 | N    |
|  2 | 10016351 | ABC          | 2016-09-01 | 2016-11-22 | N    |
|  1 | 10081503 | RTBS AMaaS   | 2016-07-18 | 2017-02-15 | O    |
|  2 | 10081503 | RTBS AMaaS   | 2015-02-03 | 2015-05-22 | N    |
|  1 | 10089293 | RTBS PDaaS   | 2017-02-17 | 2017-12-31 | N    |
|  2 | 10089293 | RTBS PDaaS   | 2016-06-13 | 2017-02-14 | N    |
+----+----------+--------------+------------+------------+------+

calendar and numbers tables reference:

• Generate a set or sequence without loops 2- Aaron Bertrand
• Creating a Date Table/Dimension in SQL Server 2008 - David Stein
• Calendar Tables - Why You Need One - David Stein
• Creating a date dimension or calendar table in SQL Server - Aaron Bertrand
• TSQL Function to Determine Holidays in SQL Server - Tim Cullen
• F_TABLE_DATE - Michael Valentine Jones