title: SQL statement to find quarters based on date range
body: <p>I have a table of user activity. I have the user ID and the date. I need to get the quarters a user had activity based on a date range i.e.:</p>
  
  <p>Date Range: 2011-08-01 - 2012-07-31</p>
  
  <pre><code>Q1 - Aug-Oct
  Q2 - Nov-Jan
  Q3 - Feb-Apr
  Q4 - May-Jul
  </code></pre>
  
  <p>I have played around with the <a href="http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_quarter" rel="nofollow">QUARTER</a> function in mysql, but that only returns the quarters based on January as the start of the year:</p>
  
  <pre><code>SELECT QUARTER(a.Date), a.Date
  FROM activity AS a
  WHERE a.InstitutionNumber = '000000000075' 
  AND a.HWA = '001372EADBC4'
  AND (a.Date between '2011-08-01' and '2012-07-31')
  GROUP BY QUARTER(a.Date), a.HWA
  </code></pre>
  
  <p>returns:</p>
  
  <pre><code>QUARTER Date
  1   2012-01-11
  3   2011-08-01
  4   2011-10-03
  </code></pre>
  
  <p>I want to be able to list the quarters that each HWA has activity as a count without overlap. What I meen by that, is that if a HWA has use in quarters 1,2 and 3 based on the August 2011-July 1012 dates, I want it to list that as 3 for quarters for that HWA.</p>
  
  <hr>
  
  <p><strong>Update:</strong></p>
  
  <p>from the below answer I now have answered the question I stated.
  I have a further complication now. Some HWAs are replacements of others. In these cases they have the same groupID to show that they are linked. In these cases, I need to cancel out overlapping quarters. To clarify, if two HWAs have usage in quarter 2 and they have the same groupID, then I need to only count quarter 2 for one of them (which does not matter). Below is the query I am now using with the output I get:</p>
  
  <pre><code> SELECT act.HWA, count(act.HWA), m.Status, m.GroupID
  FROM (
      SELECT 
        a.HWA, a.Date,
        CASE 
          WHEN MONTH(a.Date) BETWEEN 8 AND 10 THEN 'Q1'
          WHEN (MONTH(a.Date) BETWEEN 11 AND 12 OR MONTH(a.Date) = 1) THEN 'Q2'
          WHEN MONTH(a.Date) BETWEEN 2 AND 4 THEN 'Q3'
          WHEN MONTH(a.Date) BETWEEN 5 AND 7 THEN 'Q4'
        END AS quarter,
        CASE
          WHEN MONTH(a.Date) &lt;= 7 THEN YEAR(a.Date) - 1 
          ELSE EXTRACT(YEAR_MONTH from a.Date)
        END AS quarteryear,
        COUNT(*) AS num_activites
      FROM activity a
      WHERE
        a.InstitutionNumber = '000000000075'  
        AND (a.HWA = '001372EADBC4' OR a.HWA = '180373E241DB' OR a.HWA = '180373E23DE7')
        AND (a.Date between '2011-08-01' and '2012-07-31')
      GROUP BY
        quarter,
        a.HWA
  ) act, machine m
  where act.HWA = m.HWA
  group by act.HWA
  </code></pre>
  
  <p>which returns this:</p>
  
  <pre><code>HWA         quarters  status   groupid
  
  001372EADBC4    3    deleted    59970
  180373E23DE7    2     online    59970
  180373E241DB    1    deleted    59970
  </code></pre>
  
  <p>The 3 HWAs all share quarter 3 and the same groupID. Only one of them has to have Q3 in its count. You can see from above that there are 6 total quarters among the 3 HWAs. If you remove quarter 3 from two of them we have a total of 4 quarters for all 3 HWAs which is how it should be. Any further help would be wonderful. </p>