title: Finding Active Clients By Date
body: <p>I'm having trouble writing a recursive function that would count the number of active clients on any given day.</p>
  <p>Say I have a table like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Client</th>
  <th>Start Date</th>
  <th>End Date</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1-Jan-22</td>
  <td></td>
  </tr>
  <tr>
  <td>2</td>
  <td>1-Jan-22</td>
  <td>3-Jan-22</td>
  </tr>
  <tr>
  <td>3</td>
  <td>3-Jan-22</td>
  <td></td>
  </tr>
  <tr>
  <td>4</td>
  <td>4-Jan-22</td>
  <td>5-Jan-22</td>
  </tr>
  <tr>
  <td>5</td>
  <td>4-Jan-22</td>
  <td>6-Jan-22</td>
  </tr>
  <tr>
  <td>6</td>
  <td>7-Jan-22</td>
  <td>9-Jan-22</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want to return a table that would look like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Date</th>
  <th>NumActive</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1-Jan-22</td>
  <td>2</td>
  </tr>
  <tr>
  <td>2-Jan-22</td>
  <td>2</td>
  </tr>
  <tr>
  <td>3-Jan-22</td>
  <td>3</td>
  </tr>
  <tr>
  <td>4-Jan-22</td>
  <td>4</td>
  </tr>
  <tr>
  <td>5-Jan-22</td>
  <td>4</td>
  </tr>
  <tr>
  <td>6-Jan-22</td>
  <td>3</td>
  </tr>
  <tr>
  <td>7-Jan-22</td>
  <td>3</td>
  </tr>
  <tr>
  <td>8-Jan-22</td>
  <td>3</td>
  </tr>
  <tr>
  <td>9-Jan-22</td>
  <td>4</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Is there a way to do this? Ideally, I'd have a fixed start date and go to today's date.</p>
  <p>Some pieces I have tried:</p>
  <h2>Creating a recursive date table</h2>
  <p>Truncated to Feb 1, 2022 for simplicity:</p>
  <pre><code>WITH DateDiffs AS (
      SELECT DATEDIFF(DAY, '2022-02-02', GETDATE()) AS NumDays
  )
  
  , Numbers(Numbers) AS (
      SELECT MAX(NumDays) FROM DateDiffs
      UNION ALL
      SELECT Numbers-1 FROM Numbers WHERE Numbers &gt; 0 
      ) 
  
  , Dates AS (
      SELECT
          Numbers
          , DATEADD(DAY, -Numbers, CAST(GETDATE() -1 AS DATE)) AS [Date]
      FROM Numbers
  )
  </code></pre>
  <p>I would like to be able to loop over the dates in that table, such as by modifying the query below for each date, such as by @loopdate. Then UNION ALL it to a larger final query.
  I'm now stuck as to how I can run the query to count the number of active users:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT
  COUNT(Client)
  FROM clients
  WHERE [Start Date] &gt;= @loopdate AND ([End Date] &lt;= @loopdate OR [End Date] IS NULL)
  </code></pre>
  <p>Thank you!</p>