SQL query to search for quarters based on date range

Question

I have a user activity table. I have a user id and date. I need users to have activity based on ie date range:

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

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

I played with the QUARTER function in mysql, but this only returns quarters based on January as the beginning 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 in which each HWA has activity as a counter without overlapping. What I want from this is that if the HWA uses 1,2 and 3 in the quarters according to the dates of August 2011-July 1012, I want it to list it as 3 for the quarters for this HWA.

---

Update:

from the answer below, I now answered the question that I posed. I have one more complication. Some HWAs are replacements for others. In these cases, they have the same group identifier to show that they are connected. In these cases, I need to cancel the overlapping quarters. To clarify, if two HWAs have use in the second quarter, and they have the same group identifier, then I only need to read quarter 2 for one of them (which does not matter). The following is the request that I am now using with the output I receive:

  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 

3 HWAs all share a quarter of 3 and the same group identifier. Only one of them should have Q3 in its account. You can see from above that there are 6 full quarters among 3 HWAs. If you remove a quarter of 3 from two of them, we will have only 4 quarters for all 3 HWAs, as it should be. Any further help would be great.

Answer 1

You can GROUP BY column obtained using CASE to divide the quarters by months and cope with the years if the month <= July subtracts 1 from the year to build the year that begins with August (only for grouping purposes). I think this should work:

 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 understand that this is not the exact result you want. The above will give you the number of events per quarter, but you really wanted the number of quarters to have events. To get this, you can wrap the above part in a subquery and get COUNT() :

 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 

Answer 2

I know this is a little old to react to, but I did not see it as a solution, and it was easier. It can even configure it as a function.

select (DATEPART (M, A, dates), 'Q2', 'Q3', 'Q3', 'Q3', 'Q4', 'Q4', 'Q4', 'Q1', 'Q1', 'Q1' , 'Q2', 'Q2')