title: What is the most efficient way to compare a table against itself in SQL server?
body: <p>I have recently developed some coding that will identify first call resolution for an insurance company that I currently work for. The code basically creates two identical SELECT statements from a call data table, sorts and ranks the data by a <code>member_id</code>, <code>call_date</code> and <code>call_time</code> and then compares each query against one another where the member_id and call_type are equal, but the "rank" or <code>RowId</code> is not (so the comparison happens for each <code>Rowid</code> from one side of the inner join to the other but not for the same <code>Rowid</code>). This acts as a quasi "loop" to compare all the records to see where the <code>call_dates are &gt; 14</code> days OR where the <code>call_dates</code> are equal AND the <code>call_times are &gt; 24</code> hours....for the same <code>call_type</code> and same <code>member_id</code>.</p>
  
  <pre><code>Here is a sample of the code:
  
  DECLARE @Temp TABLE (
  [MEM_ID] [varchar](10) NULL,
         [CALL_DATE] [datetime2](3) NULL,
         [CALL_TIME] [varchar](8) NULL,
         [OPER_CODE] [varchar](8) NULL,
         [TEXT_DEPT] [varchar](6) NULL,
         [CAT_DESC] [varchar](30) NULL
  )
  
  INSERT INTO @Temp
  VALUES ('00000-1400', '2018-07-16 00:00:00.000','10:12:23','YYZ2500','SERV06','PLAN BENEFITS')
  ,('00000-1400', '2018-06-10 00:00:00.000','10:12:23','YYZ2500','SERV06','PLAN BENEFITS')
  ,('00000-1400', '2018-07-01 00:00:00.000','18:12:23','YYZ2500','SERV06','CLAIMS')
  ,('00000-1400', '2018-07-02 00:00:00.000','05:12:23','YYZ2500','SERV06','OTHER')
  ,('00000-1400', '2018-07-14 00:00:00.000','02:12:23','YYZ2500','SERV06','CLAIMS')
  ,('00000-1400', '2018-07-27 00:00:00.000','11:12:23','YYZ2500','SERV06','PLAN BENEFITS')
  ,('00000-1400', '2018-06-30 00:00:00.000','08:12:23','YYZ2500','SERV06','PLAN BENEFITS')
  ,('00000-1400', '2018-06-29 00:00:00.000','07:12:23','YYZ2500','SERV06','AUTHORIZATIONS')
  ,('00000-1400', '2018-06-29 00:00:00.000','07:26:23','YYZ2500','SERV06','AUTHORIZATIONS')
  ,('00000-1400', '2018-06-25 00:00:00.000','09:38:23','YYZ2500','SERV06','OTHER')
  
  
  Select Calc.*, CASE WHEN Calc.Disposition = 'No subsequent within 14' THEN CONVERT(tinyint, 1) ELSE 0 END as FCR_Ind
  
  From  (Select DISTINCT Final.RowID, Final.CallCount, Final.MEM_ID, Final.CALL_DATE, 
      Final.CALL_TIME, Final.CAT_DESC, Final.OPER_CODE, Final.TEXT_DEPT,
         CASE WHEN Final.[Had a subsequent within 14 days] IS NULL 
         THEN 'No subsequent within 14' ELSE Final.[Had a subsequent within 14 days]
         END as Disposition
         From  (Select *
   From (Select DISTINCT T1.RowID, T1.CallCount, T1.MEM_ID, T1.CALL_DATE, 
      T1.CALL_TIME, T1.CAT_DESC, T1.OPER_CODE, T1.TEXT_DEPT,
  CASE WHEN T2.CALL_DATE BETWEEN T1.CALL_DATE AND DateAdd(DAY, 14, T1.CALL_DATE) AND T2.RowID &lt;&gt; T1.RowID AND T2.CALL_DATE &lt;&gt; T1.CALL_DATE OR T2.CALL_TIME BETWEEN T1.CALL_TIME AND DateAdd(HOUR, 24, T1.CALL_DATE) AND T2.RowID &lt;&gt; T1.RowID AND T2.CALL_DATE = T1.CALL_DATE THEN 'Had a subsequent within 14 days' END as Disposition                                                           
                 From   (Select ROW_NUMBER() OVER (PARTITION BY MEM_ID ORDER BY MEM_ID,
                [CALL_DATE], CALL_TIME) AS RowID, Count(CALL_TIME) as CallCount,
    MEM_ID, [CALL_DATE], CALL_TIME, CAT_DESC, OPER_CODE, TEXT_DEPT
                         From @Temp
                         Group by MEM_ID, [CALL_DATE], CALL_TIME, CAT_DESC, OPER_CODE, 
                      TEXT_DEPT) as T1
          Inner Join  (Select ROW_NUMBER() OVER (PARTITION BY MEM_ID ORDER BY MEM_ID, 
                  [CALL_DATE], CALL_TIME) AS RowID, Count(CALL_TIME) as
                  CallCount, MEM_ID, [CALL_DATE], CALL_TIME, CAT_DESC,
                  OPER_CODE, TEXT_DEPT
      From @Temp
                  Group by MEM_ID, [CALL_DATE], CALL_TIME, CAT_DESC, OPER_CODE,
          TEXT_DEPT) as T2
                        ON   T1.MEM_ID = T2.MEM_ID AND T1.CAT_DESC = T2.CAT_DESC ) as Sub 
  
        PIVOT( MAX(Sub.Disposition)
  FOR Sub.Disposition IN ([Had a subsequent within 14 days])) AS PivotTable) as Final) as Calc
  </code></pre>
  
  <p>The Output yields the following:
  <img src="https://i.stack.imgur.com/pklS7.jpg" alt="SQL output"></p>
  
  <p>I know this is not the most efficient way to solve this problem and was wondering if there is a way to write the INNER JOIN portion where only 1 of the sorted tables is used to iterate thru out the records of the 2nd sorted table instead of each table iterating against each other? I appreciate any insights on how to make this code use less system resources and make it more efficient from an execution plan standpoint. 
      Thanks!! </p>