title: SQL to get an daily average from month total
body: <p>I have a table that lists month totals (targets)</p>
  
  <pre><code>person      total                 month       
  ----------- --------------------- ----------- 
  1001        114.00                201005      
  1001        120.00                201006      
  1001        120.00                201007      
  1001        120.00                201008      
  .
  1002        114.00                201005      
  1002        222.00                201006      
  1002        333.00                201007      
  1002        111.00                201008      
  .
  .
  </code></pre>
  
  <p>but month is an integer(!)</p>
  
  <p>I also have another table that has a list of working days (calendar)</p>
  
  <pre><code>tran_date               day_type
  ----------------------- ---------------------------------
  1999-05-01 00:00:00.000 WEEKEND
  1999-05-02 00:00:00.000 WEEKEND
  1999-05-03 00:00:00.000 WORKING_DAY
  1999-05-04 00:00:00.000 WORKING_DAY
  
  1999-06-01 00:00:00.000 .....
  .
  .
  .
  </code></pre>
  
  <p>What I want to do is get a list of dates with the average for that day based on the number of days in the month where day_type is 'WORKING_DAY' /  the month's total.</p>
  
  <p>so if I had say 20 working days in 201005 then I'd get an average of 114/20 on each working day, while the other days would be 0.</p>
  
  <p>somthing like </p>
  
  <pre><code>person   tran_date               day_avg
  -------  ----------------------- ---------------------------------
  1001     2010-05-01 00:00:00.000 0
  1001     2010-05-02 00:00:00.000 0
  1001     2010-05-03 00:00:00.000 114/2 (as there are two working days)
  1001     2010-05-04 00:00:00.000 114/2 (as there are two working days)
  .
  .
  .
  </code></pre>
  
  <p>It has to be done as a CTE as this is a limitation of the target system (I can only do one statement)
  I can start off with (Dates to </p>
  
  <pre><code>WITH 
  Dates AS
  (
      SELECT CAST('19990501' as datetime) TRAN_DATE
      UNION ALL
      SELECT TRAN_DATE + 1
      FROM Dates
      WHERE TRAN_DATE + 1 &lt;= CAST('20120430' as datetime)
  ),
  Targets as
  (
     select CAST(cast(month as nvarchar) + '01' as dateTime) mon_start, 
              DATEADD(MONTH, 1, CAST(cast(month as nvarchar) + '01' as dateTime)) mon_end, 
               total
     from targets
  )
  select ????
  </code></pre>