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

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!

1
задан Ed Harper 28 September 2010 в 12:12
поделиться