The need for date validation overlaps or not in sql server

Use outer apply()

to return the first one id

to overlap and group by this value. Using row_number()

to renumber 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

      

        
        
        
      

    

Demo version of the rexter: 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 cause the overlapping ranges to be flattened, but if you need to collapse multiple ranges that only overlap at the edges into one range, we can use a calendar or dates table like this:

For just 152KB in memory, you can have 30 year dates in your 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 don't want to create a date table, you can use this query to create dates like this:

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)

      

        
        
        
      

    

registry: 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    |
+----+----------+--------------+------------+------------+------+

      

        
        
        
      

    

directory of calendar and table numbers:

• Create set or sequence without loops 2- Aaron Bertrand
• Creating a Date / Dimension Table in SQL Server 2008 - David Stein
• Calendar tables - why you need - David Stein
• How to size a date or calendar table in SQL Server - Aaron Bertrand
• TSQL Function for Determining Holidays in SQL Server - Tim Cullen
• F_TABLE_DATE - Michael Valentine Jones