title: Count number of rows between start and end date
body: <p>I have a table with few hundred thousand rows, with columns containing a start and finish datetime, something like this:</p>
  
  <pre><code>ID    StartDateTime              FinishDateTime
  --------------------------------------------------------
  1     2001-01-01 04:05:06.789    2001-02-03 04:05:06.789
  2     2001-01-01 05:05:06.789    2001-01-01 07:05:06.789
  3     2001-01-01 06:05:06.789    2001-02-04 07:05:06.789
  4     2001-03-01 06:05:06.789    2001-02-03 04:05:06.789
  </code></pre>
  
  <p>For each row, I need to count the number of 'active' rows at the start time; as in count rows that start before and finish after the startdatetime for each row. For instance: for ID=3, the startdatetime falls between the startdatetime and finishdatetime of ID=1 and ID=2, but not ID=3 or ID=4, so it should return 2.</p>
  
  <p>The desired output is:</p>
  
  <pre><code>ID    ActiveRows
  -----------------
  1     0
  2     1
  3     2
  4     0
  </code></pre>
  
  <p>I can get it to work using the query below, but it takes hours to run. </p>
  
  <pre><code>select
      ID,
      (select count(1) 
       from table tbl2 
       where tbl2.StartDateTime &lt; tbl.StartDateTime 
         and tbl2.FinishDateTime &gt; tbl.StartDateTime) as 'ActiveRows'
  from 
      table tbl
  </code></pre>
  
  <p>I've also tried joining the table on itself, but it also seems extremely slow. </p>
  
  <pre><code>select
      tbl.ID, count(1)
  from 
      table tbl
  left join table 
      tbl2 on tbl2.StartDateTime &lt; tbl.StartDateTime 
           and tbl2.FinishDateTime &gt; tbl.StartDateTime
  group by 
      tbl.ID
  </code></pre>
  
  <p>What is the fastest way to perform this calculation?</p>