title: How do I get a recursive daily average for one month?
body: <p>I need a daily average for an entire month, but the trick is that all of the clients have different start and end dates.  For example, some clients are only enrolled for part of the month.  Assume client A is enrolled from 4/3/13-4/8/13, client B from 4/6-4/30, client C from 4/1-5/1, etc.  How can I achieve this?  Here is my current code which returns super low counts because it assumes all clients are enrolled the entire month:</p>
  
  <pre><code>if exists (
      select  * from tempdb.dbo.sysobjects o    where o.xtype in ('U')  and o.id = object_id(N'tempdb..#enrollments_PreviousMonth2')
  ) DROP TABLE #enrollments_PreviousMonth2;
  
  Select
      people_id,
      program_modifier,
      program_modifier_id,
      DATEADD(dd, 0, DATEDIFF(dd, 0, actual_date)) as enroll_midnight_date,
      actual_date as enroll_start_date,
      end_date as enroll_end_date
  
  INTO #enrollments_PreviousMonth2 
  From
   program_modifier_enrollment_view pmev with(nolock) 
  Where
  program_modifier_id = 'E1AA7A36-0500-4BAE-A0AA-D9E0BC91A6F3' and
  actual_date  &lt;= '4/30/13' and (end_date &gt;= '4/1/13' or end_date is null)
  
  ;with cte as (
  select cast(enroll_start_date as date) as actual_date,
      count(people_id) cnt
  From #enrollments_PreviousMonth2 en
  left join Calendar c on en.enroll_midnight_date = c.dt
  where program_modifier_id = 'E1AA7A36-0500-4BAE-A0AA-D9E0BC91A6F3' 
      AND enroll_start_date  &lt;= '4/30/13' and (enroll_end_date &gt;= '4/1/13' or enroll_end_date is null)
  Group by enroll_start_date--, enroll_end_date, program_modifier_id, program_modifier
  )
  select  
  sum(cnt*1.0)
  from cte
  </code></pre>
  
  <p>I prefer to not use a CURSOR for the solution, however.  </p>