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.:

Date Range: 2011-08-01 - 2012-07-31

```
Q1 - Aug-Oct
Q2 - Nov-Jan
Q3 - Feb-Apr
Q4 - May-Jul

```

I have played around with the [QUARTER](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_quarter) function in mysql, but that only returns the quarters based on January as the start of the year:

```
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

```

returns:

```
QUARTER Date
1   2012-01-11
3   2011-08-01
4   2011-10-03

```

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.

---

**Update:**

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:

```
 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) <= 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

```

which returns this:

```
HWA         quarters  status   groupid

001372EADBC4    3    deleted    59970
180373E23DE7    2     online    59970
180373E241DB    1    deleted    59970

```

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.