title: Difficult SELECT statement with only two columns
body: <p>I have an SQL Server table structured as follows :</p>
  
  <pre><code>Table name : calendar. 
  </code></pre>
  
  <p>Columns : </p>
  
  <pre><code>Calendar Date (smalldatetime)
  Working Day (bit)
  </code></pre>
  
  <p>Calendar date has all dates, structured in the format yyyy-mm-dd. Working day means that I have work if it is a 1, and if it is a weekend or a holiday it is marked as a 0 :).</p>
  
  <p>Example of what I want to retrieve :</p>
  
  <pre><code>Date   NumWorkingDaysInMonthSoFar
  ------------------------------------
  2013-06-01    0 --Due to this being marked a 0 as above (Saturday)
  2013-06-02    0 --Due to this being marked a 0 as above (Sunday) -- All 3 of these dates marked as 0
  2013-06-03    1 --Due to this being marked a 0 as above (Bank Holiday)
  2013-06-04    1
  2013-06-05    2
  2013-06-06    3
  </code></pre>
  
  <p>Except I would like the query to work for ALL dates in the calendar - so if I ran it today, it would retrieve the number of working days in the month containing all of the calendar dates in my table above.</p>
  
  <p>All of the information is there, but I am just not sure how to write a query like this or even where to begin, but I think I have gotten the barebones of it below : </p>
  
  <pre><code>SELECT Sum(Working Day)
         WHERE DATEADD(dd, 0, DATEDIFF(mm, 0, CalendarDate)) between
                GETDATE() and START_OF_MONTH
         GROUP BY (Not a clue)
  </code></pre>