title: Date Range Intersections
body: <p>I have a BreakdownLog table with the following columns:</p>
  
  <pre><code>EquipmentID, ProblemID, BreakdownDate, IssueFixedDate
  </code></pre>
  
  <p>Each equipment can have multiple breakdowns, and more importantly, there could be overlapping date ranges for the same equipment!</p>
  
  <p>For example, given below data:</p>
  
  <pre><code>EquipmentID|ProblemID|BreakdownDate|IssueFixedDate
  1          |1        |01-Jun-2011  |01-Sep-2011
  1          |2        |01-Jun-2011  |01-Oct-2011
  2          |1        |01-Jun-2011  |01-Oct-2011
  2          |2        |01-Jun-2011  |01-Oct-2011
  3          |1        |15-Jun-2011  |01-Sep-2011
  3          |2        |10-Jun-2011  |25-Aug-2011
  4          |1        |01-Jun-2011  |01-Aug-2011
  4          |2        |10-Sep-2011  |22-Oct-2011
  5          |1        |01-Jun-2011  |15-Jun-2011
  5          |2        |02-Jun-2011  |NULL
  </code></pre>
  
  <p>Now I want a query which can compute the number of days each equipment was defunct. If IssueFixedDate is NULL, we assume that the equipment is still defunct and therefore compute Defunct days upto current date.</p>
  
  <p>The expected result set should be:</p>
  
  <pre><code>EquipmentID|DefunctDays
  1          |122
  2          |122
  3          |83
  4          |103
  5          |143
  </code></pre>
  
  <p>I am using SQL Server 2008. So even CTEs, cursors etc are acceptable.</p>
  
  <p>Thanks<br>
  Raghu</p>