Grouping totals by date ranges

Question

Hi All, I have to get totals from a table using different criteria, which I do like this:

SELECT DISTINCT
  SUM(CASE WHEN MYCONDITION1 THEN 1 ELSE 0 END) AS TOTAL1,
  SUM(CASE WHEN MYCONDITION2 THEN 1 ELSE 0 END) AS TOTAL2
FROM TABLE1, TABLE2
WHERE COMMON_CONDITION1 AND COMMON_CONDITION2
AND BETWEEN DATE1 AND DATE2;

This works fine and I get the intended result.

Now, I have to repeat this for every week for the last 12 months, excluding holidays period. So, I generate a set of date ranges which will be used in the queries. So, I repeat the above sql statement for all the date ranges, which is a lengthy process.

I have to get the totals for every week. For example from 26-Sep-2010 to 02-Oct-2010, 19-Sep-2010 to 25-Sep-2010, 12-Sep-2010 to 18-Sep-2010, etc.. How should I put those ranges in the query for grouping and in the select list, as I don't have them in a table.

How can I do that in a single shot and get all totals for each date range. Please help me with sql.

Thank you.

Answer 1

You could create a table that contains the date ranges. You can join on the new table; you'll have to replace distinct with group by. For example:

SELECT  TABLE3.DATE1
,       TABLE3.DATE2
,       SUM(CASE WHEN MYCONDITION1 THEN 1 ELSE 0 END) AS TOTAL1
,       SUM(CASE WHEN MYCONDITION2 THEN 1 ELSE 0 END) AS TOTAL2
FROM    TABLE1, TABLE2, TABLE3
WHERE   COMMON_CONDITION1 
        AND COMMON_CONDITION2
        AND DATE_COLUMN BETWEEN TABLE3.DATE1 AND TABLE3.DATE2
GROUP BY
        TABLE3.DATE1
,       TABLE3.DATE2