title: Left joins on the same table with date ranges
body: <p>I have looked for this solution but none was a good match for my particular problem.</p>
  
  <p>I am looking at help desk data and want to identify those customers that have called in about the same issue within a 28 day period (-14 prior or +14 days after the incident).</p>
  
  <p>The attempt is to calculate resolution rate and first call resolution. I am using one table that I am self joining.</p>
  
  <p>Here is a sample of the data:</p>
  
  <pre><code>CustID | TICKET |Code       |Date
  01     | 001    |Benefits   |11/01/2015
  01     | 001    |Transport  |11/01/2015
  01     | 009    |Benefits   |11/13/2015
  01     | 010    |Claims     |11/14/2015
  01     | 010    |Transport  |11/30/2015
  02     | 010    |Benefits   |11/30/2015
  </code></pre>
  
  <p>Then end result I want is all the original values and appended to it all the other rows that meet the criteria of the same customer, same issue but only those instances that occurred 14 prior or after the incident date:</p>
  
  <pre><code>CustID | TICKET |Code     |Date      |CustID2 |Ticket2|Code2   |Date2
  01     | 001    |Benefits |11/01/2015|01      |009    |Benefits|11/13/2015
  01     | 001    |Transport|11/01/2015|NULL    |NULL   |NULL    |NULL
  01     | 009    |Benefits |11/13/2015|01      |001    |Benefits|11/01/2015
  01     | 010    |Claims   |11/14/2015|NULL    |NULL   |NULL    |NULL
  01     | 001    |Transport|11/30/2015|NULL    |NULL   |NULL    |NULL
  02     | 010    |Benefits |11/30/2015|NULL    |NULL   |NULL    |NULL
  </code></pre>
  
  <p>I tried using a left join but the end result is similar to an inner join because of the data range.</p>
  
  <pre><code>SELECT 
  T1.CustID, 
  T1.TICKET, 
  T1.Code, 
  T1.Date, 
  T2.CustID AS CustID2, 
  T2.TICKET As Ticket2, 
  T2.Code As Code2, 
  T2.Date AS Date2
  
  FROM TABLE AS T1 
  LEFT JOIN TABLE AS T2 
  ON T1.CustID = T2.CustID AND T1.Code = T2.Code 
  WHERE 
  T1.TICKET &lt;&gt; T2.TICKET
  AND T2.Date Between T1.Date-14 
  AND T1.Date+14
  </code></pre>
  
  <p>I am thinking I might have to do this as two queries but while I can get all the results that match my criteria I cannot get those that do not match.</p>