title: SQL Server : How to count rows within a given date range?
body: <p>I have a table:</p>
  
  <pre><code>ID     StartDate       EndDate
  
  1      2016-01-01      2016-01-03
  2      2016-01-01      2016-01-01
  3      2016-01-01      2016-01-01
  4      2016-01-02      2016-01-02
  </code></pre>
  
  <p>I want to generate a second table which indicates, for every day of the year, the number of rows which include that calendar date within the period between the start and end dates, something like this:</p>
  
  <pre><code>WorkDate        NumActive
  
  2016-01-01      3
  2016-01-02      2
  2016-01-03      1
  </code></pre>
  
  <p>Currently I am trying:</p>
  
  <pre><code>SELECT      COUNT(ID)
  FROM        TableOne
  WHERE       StartDate   &gt;=  WorkDate
          and EndDate     &lt;=  WorkDate
  </code></pre>
  
  <p>This is giving me zeros for every day of the year.</p>
  
  <p>Any help is appreciated - I'm self-taught via StackOverflow and I'm  a bit over my head here.</p>