How to get unique rows from TSQL based on date open and closed

Question

I have two tables this is data from one:

ID          ANS_ID       USER_ID  Date_Opened
06146723    858735205    55258    2009-02-20 12:59:47.0000000
06146723    481768765    55258    2009-09-16 17:04:22.0000000

and table 2:

ID          ANS_ID     USER_ID  Date_Closed
06146723    630993597  5258     2009-04-02 14:35:23.0000000
06146723    1348252927 5258     2010-05-24 16:03:33.0000000

I need to combine them and get one record, per close and open joint. I tried this:

select distinct
        a.ID
       ,a.ANS_ID
       ,a.USER_ID
       ,a.Date_Opened
       ,b.Date_Closed
       ,b.ANS_ID
 from  Table1 a inner join Table2 b on a.ID = b.ID and a.Date_Opened < b.Date_Closed
 order by a.ID, a.Date_Opened

and I got:

  06146723  858735205    55258  2009-02-20 12:59:47.0000000 2009-04-02 14:35:23.0000000 630993597
**06146723  858735205    55258  2009-02-20 12:59:47.0000000 2010-05-24 16:03:33.00000001348252927**
  06146723  481768765    55258  2009-09-16 17:04:22.0000000 2010-05-24 16:03:33.00000001348252927

How can I remove a Middle row?

Thank You!

Answer 1

;with cte as
(
select 
        a.ID
       ,a.ANS_ID AS a_ANS_ID
       ,a.USER_ID
       ,a.Date_Opened
       ,b.Date_Closed
       ,b.ANS_ID AS b_ANS_ID
       ,ROW_NUMBER() over (order by a.Date_Opened asc, b.Date_Closed asc) as rn1
       ,ROW_NUMBER() over (order by b.Date_Closed desc, a.Date_Opened desc) as rn2
 from  Table1 a inner join Table2 b on a.ID = b.ID and a.Date_Opened < b.Date_Closed
)
select ID, a_ANS_ID, USER_ID, Date_Opened, Date_Closed, b_ANS_ID
from cte
where rn1 = 1 or rn2=1
order by ID, Date_Opened