title: How can I return rows which match on some columns and fulfil a DateTime comparison between two other columns using SQL?
body: <p>I have a table which contains rows for jobs, example below, where 01/01/1980 is used rather than null in the ClosedDate column for jobs which are not finished:</p>
  
  <pre><code>JobNumber  JobCategory  CustomerID  CreatedDate  ClosedDate
  1          Small        1           01/01/2016   03/01/2016
  2          Small        2           03/01/2016   07/01/2016
  3          Large        2           06/01/2016   07/01/2016
  4          Medium       1           08/01/2016   10/01/2016
  5          Small        3           10/01/2016   01/01/1980
  6          Medium       3           15/01/2016   01/01/1980
  7          Large        2           16/01/2016   17/01/2016
  8          Large        2           19/01/2016   20/01/2016
  9          Small        1           19/01/2016   01/01/1980
  10         Medium       2           19/01/2016   01/01/1980
  </code></pre>
  
  <p>I need to return a list of any jobs where the same customer has had a job of the same category created within 3 days of the previous job being closed.</p>
  
  <p>So, I would want to return:</p>
  
  <pre><code>7          Large        2           16/01/2016   17/01/2016
  8          Large        2           19/01/2016   20/01/2016
  </code></pre>
  
  <p>because Customer 2 had a Large job closed on 17/01/2016 and another Large job opened on 19/01/2016, which is within 3 days.</p>
  
  <p>In order to do this, I assume I need to compare each record in the table with each subsequent record, looking for a match on JobCategory and comparing CreatedDate with ClosedDate between rows.</p>
  
  <p>Can anyone advise my best option for this using SQL? I'm using SQL Server 2012.</p>