SQL statement to find quarters based on date range

Question

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

Answer 1

You can GROUP BY a column derived with a CASE to separate out the quarters by month and to deal with the years, if the month <= July, subtract 1 from the year to build a year that starts in August (just for grouping purposes). I think this should do the job:

SELECT 
  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
    /* Make a year that starts with August so quarters appear to be all in the same year */
    WHEN MONTH(a.Date) <= 7 THEN YEAR(a.Date) - 1 
    ELSE YEAR(a.Date) 
  END AS quarteryear,
  COUNT(*) AS num_activites
FROM Activity a
WHERE
  a.InstitutionNumber = '000000000075'  
  AND a.HWA = '001372EADBC4'
  AND (a.Date between '2011-08-01' and '2012-07-31')
/* Groupings over the quarter and fake year for aggregates */
GROUP BY
  quarter, 
  quarteryear

Update:

Reread your question and I realize this isn't the exact output you want. The above will give you the number of events per quarter, but you really wanted the number of quarters having events. To get that, you can wrap the above part in a subquery and get COUNT() on that:

SELECT quarteryear, COUNT(*)
FROM (
    SELECT 
      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
        /* Make a year that starts with August so quarters appear to be all in the same year */
        WHEN MONTH(a.Date) <= 7 THEN YEAR(a.Date) - 1 
        ELSE YEAR(a.Date) 
      END AS quarteryear,
      COUNT(*) AS num_activites
    FROM Activity a
    WHERE
      a.InstitutionNumber = '000000000075'  
      AND a.HWA = '001372EADBC4'
      AND (a.Date between '2011-08-01' and '2012-07-31')
    /* Groupings over the quarter and fake year for aggregates */
    GROUP BY
      quarter, 
      quarteryear
) qtrsubcounts
GROUP BY quarteryear