title: SQL - Count and group records by month and field value from the last year
body: <p>I need to count totals number of records in a table, 'a', where a field in 'a', say 'type', has a certain value, 'v'. From all these records where a.type = 'v', I need to group these twice: first by field 'b_id', and again by month. The date range for these records must be restricted to the last year <em>from the current date</em></p>
  
  <p>I already have the totals for the 'b_id' field with ISNULL() as follows:</p>
  
  <pre><code>SELECT ISNULL(
      SELECT COUNT(*)
      FROM a
      WHERE a.type = 'v'
      AND b.b_id = a.b_id
  ), 0) AS b_totals
  </code></pre>
  
  <p>The data lies in table a, and is joined on table b. 'b_id' is the primary key for table b, and is found in table a (thought it is not part of a's key). The key for a is irrelevant to the data I need to pull, but can be stated as "a_id" for simplicity.</p>
  
  <p>How do I:</p>
  
  <ul>
  <li>Restrict these records to the past twelve months from the current date.</li>
  <li>Take the total for any and all values of b.id, and categorize them by month. This is in addition to the totals of b.id by year. The date is stored in field "date_occurred" in table 'a' as a standard date/time type.</li>
  </ul>
  
  <p>The schema at the end should look something like this, assuming that the current month is October and the year is 2016:</p>
  
  <pre><code>b.id | b_totals | Nov. 2015 | Dec. 2015 | Jan. 2016 .... Oct. 2016
  __________________________________________________________________
  ID_1      1           0         0           0               1
  ID_2      3           2         0           1               0
  ID_3      5           1         1           3               0
  </code></pre>
  
  <p>EDIT: I should probably clarify that I'm counting the records in table 'a' where field 'f' has a certain value 'v.' From these records, I need to group them by building then by month/date. I updated my ISNULL query to make this more clear, as well as the keys for a and b. "date_occured" should be in table a, not b, that was a mistake/typo on my end.</p>
  
  <p>If it helps, the best way I can describe the data from a high level without giving away any sensitive data:</p>
  
  <ul>
  <li>'b' is a table of locations, and 'b.b_id' is the ID for each location</li>
  <li>'a' is a table of events. The location for these events is found in 'a.b_id' and joined on 'b.b_id' The date that each event occured is in 'a.date_occurred'</li>
  <li>I need to restrict the type of events to a certain value. In this case, the type is field 'type.' This is the "where" clause in my ISNULL SQL query that gets the totals by location.</li>
  <li>From all the events of this particular type, I need to count how many times this event occurred in the past year for each location. Once I have these totals from the past year, I need to count them by month.</li>
  </ul>
  
  <p>Table structure:
  The table structure of a is something like</p>
  
  <p>a.a_id | a.b_id | a.type | a.date_occurred</p>
  
  <p>Again, I do not need the ID's from a: just a series of counts based on type, b_id, and date_occurred.</p>
  
  <p>EDIT 2: I restricted the totals of b_id to the past year with the following query:</p>
  
  <pre><code>SELECT ISNULL(
      SELECT COUNT(*)
      FROM a
      WHERE a.type = 'v'
      AND b.b_id = a.b_id
      AND a.date_occurred BETWEEN (DATEADD(yyyy, -1, GETDATE()) AND (GETDATE())
  ), 0) AS b_totals
  </code></pre>
  
  <p>Now need to do this with a PIVOT and the months.</p>