Q:

Need to select records that have overlapping date ranges for same product groupings

create table #productcov  
(  
[group] varchar(20), -- as business  
account varchar(10), -- as business location  
member  varchar(10), -- as member  
product varchar(10), -- as health product  
[plan]  varchar(10), -- as product type  
StartTime datetime,  -- as beginning of coverage period  
EndTime datetime     -- as end of coverage period  
) 
go  

insert into #productcov values 

('NN00001','10001','1234567890','ADD','A1000','2010-10-01 00:00:00' , '2012-09-30 00:00:00'),  
('NN00001','10001','1234567890','ADD','A1000','2012-10-01 00:00:00' , '2013-09-30 00:00:00'),  
('NN00001','10001','1234567890','ADD','A1000','2013-01-01 00:00:00' , '2013-12-31 00:00:00'),   
('NN00001','10001','1234567890','ADD','B1000','2010-01-01 00:00:00' , '2014-12-31 00:00:00'),    
('NN00001','10001','1234567890','ADD','B1000','2015-01-01 00:00:00' , '2016-12-31 00:00:00'),  
('NN00001','10001','1234567890','LIF','A1000','2010-10-01 00:00:00' , '2012-09-30 00:00:00'),  
('NN00001','10001','1234567890','LIF','A1000','2012-10-01 00:00:00' , '2013-09-30 00:00:00'),  
('NN00001','10001','1234567890','LIF','A1000','2013-01-01 00:00:00' , '2013-12-31 00:00:00'),  
('NN00001','10001','1234567890','LIF','B1000','2010-01-01 00:00:00' , '2014-12-31 00:00:00'),  
('NN00001','10001','1234567890','LIF','B1000','2015-01-01 00:00:00' , '2016-12-31 00:00:00')

select * from   #productcov

The example table above is for a single member in a single group.  I am trying to select just the second and third and seventh and eighth records from the ten records in the #productcov table where the coverage dates of the medical products are overlapping for a member's products.  This is an audit process to clean up overlapping data in coverage dates.  The overlapping dates in the table that are significant needs to be grouped by Group, Account, Member, Product, and Plan. 
I've been trying to find a solution for days and am coming up blank!  Any help is appreciated. 
The desired output would be the 4 records listed below:
Group    , Account, Member, Product, Plan, StartDate, EndDate
'NN00001','10001','1234567890','ADD','A1000','2012-10-01 00:00:00' , '2013-09-30 00:00:00',
'NN00001','10001','1234567890','ADD','A1000','2013-01-01 00:00:00' , '2013-12-31 00:00:00'
'NN00001','10001','1234567890','LIF','A1000','2012-10-01 00:00:00' , '2013-09-30 00:00:00'
'NN00001','10001','1234567890','LIF','A1000','2013-01-01 00:00:00' , '2013-12-31 00:00:00' 

A:

I was finally able to figure out this audit on my own for our production database!  I used multiple Common Table Expressions, Dense_Ranks, Row_Numbers all in one large query to solve!  Will post results of the test data query later. 
with cte as ( 
   select distinct 
   DENSE_RANK() OVER (ORDER BY CONCAT(rtrim([group]),rtrim    (account),rtrim(member), rtrim(product), rtrim([plan]))) AS [Key Count],
   ROW_NUMBER() OVER (PARTITION BY CONCAT(rtrim([group]),rtrim(account), rtrim(member), rtrim(product), rtrim([plan])) order by CONCAT(rtrim([group]),rtrim(account), rtrim(member), rtrim(product), rtrim([plan])), StartTime) as [Overlap],
   CONCAT(DENSE_RANK() OVER (ORDER BY CONCAT(rtrim([group]),rtrim(account), rtrim(member), rtrim(product), rtrim([plan]))), ROW_NUMBER() OVER (PARTITION BY CONCAT(rtrim([group]),rtrim(account), rtrim(member), rtrim(product), rtrim([plan])) order by CONCAT(rtrim([group]),rtrim(account), rtrim(member), rtrim(product), rtrim([plan])), StartTime)) as Identifier,
   CONCAT(rtrim([group]),rtrim(account),rtrim(member), rtrim(product), rtrim([plan])) as [Key],
   StartTime as [Eff Date],
   case when EndTime = 0 then '29991231' else EndTime end as [Term Date],
   [group],
   account,
   member,
   product,
   [plan]
from   #productcov
where  [group] like 'N%'), 

cte2 as (
   select distinct 
   DENSE_RANK() OVER (ORDER BY CONCAT(rtrim([group]),rtrim(account),rtrim(member), rtrim(product), rtrim([plan]))) AS [Key Count],
   ROW_NUMBER() OVER (PARTITION BY CONCAT(rtrim([group]),rtrim(account), rtrim(member), rtrim(product), rtrim([plan])) order by CONCAT(rtrim([group]),rtrim(account), rtrim(member), rtrim(product), rtrim([plan])), StartTime) as [Overlap],
   CONCAT(DENSE_RANK() OVER (ORDER BY CONCAT(rtrim([group]),rtrim(account), rtrim(member), rtrim(product), rtrim([plan]))), ROW_NUMBER() OVER (PARTITION BY CONCAT(rtrim([group]),rtrim(account), rtrim(member), rtrim(product), rtrim([plan])) order by CONCAT(rtrim([group]),rtrim(account), rtrim(member), rtrim(product), rtrim([plan])), StartTime)) as Identifier,
   CONCAT(rtrim([group]),rtrim(account),rtrim(member), rtrim(product), rtrim([plan])) as [Key],
   StartTime as [Eff Date],
   case when EndTime = 0 then '29991231' else EndTime end as [Term Date],
   [group],
   account,
   member,
   product,
   [plan]
from   #productcov
where  [group] like 'N%')

select distinct 
            cte2.[Group], 
            cte2.[Account], 
            cte2.[Member],
            cte2.[Product],
            cte2.[Plan]
from   cte
left outer join cte2 on cte.[Key] = cte2.[Key]
where  (cte2.[Eff Date] between cte.[Eff Date] and cte.[Term Date]
or      cte2.[Term Date] between cte.[Eff Date] and cte.[Term Date])
and    cte.[Key Count] = cte2.[Key Count]
and    cte.[Identifier] <> cte2.[Identifier]
group by cte2.[Group], cte2.[Account], cte2.[Member], cte2.[Product], cte2.[Plan]
order by cte2.[Group], cte2.[Account], cte2.[Member], cte2.[Product], cte2.[Plan];

