title: ORACLE Group query by week, month and year
body: <p>I have a bunch of queries that take data with a time stamp and spit out SUMS based the last few weeks, months, and year to date. It looks like this</p>
  
  <pre><code>Week1    Sum for most recent week
  Week2    Sum for second most recent week
  WeekN    Sum for N most recent week
  Jan-Dec  Sum for January-December
  YTD      Sum for everything this year
  </code></pre>
  
  <p>This is how the query currently does this</p>
  
  <pre><code>SELECT TIME_PERIOD, INDEX, SUM(ITEM)   
  FROM (SELECT
          INDEX ,
          (CASE  
              WHEN ACTIVITY_DAY&gt;=(TO_DATE( :end_day,
              'yyyy-mm-dd' )-6)  
              AND ACTIVITY_DAY&lt;=(TO_DATE( :end_day,
              'yyyy-mm-dd' )-0) THEN 'WEEK1'  
              WHEN ACTIVITY_DAY&gt;=(TO_DATE( :end_day,
              'yyyy-mm-dd' )-13)  
              AND ACTIVITY_DAY&lt;=(TO_DATE( :end_day,
              'yyyy-mm-dd' )-7) THEN 'WEEK2'  
              ELSE NULL  
          END) AS TIME_PERIOD,
          MAX(ITEMS) AS ITEM
      FROM
          SOURCE  
      GROUP BY
          INDEX ,
          DAY  
      UNION
      ALL SELECT
          INDEX ,
          (CASE  
              WHEN ACTIVITY_DAY&gt;=TO_DATE( :year||'-01-01',
              'yyyy-mm-dd' )  
              AND ACTIVITY_DAY&lt;=TO_DATE( :year||'-01-31',
              'yyyy-mm-dd' ) THEN 'Jan'  
              WHEN ACTIVITY_DAY&gt;=TO_DATE( :year||'-02-01',
              'yyyy-mm-dd' )  
              AND ACTIVITY_DAY&lt;TO_DATE( :year||'-03-01',
              'yyyy-mm-dd' ) THEN 'Feb'   
              ELSE NULL  
          END) AS TIME_PERIOD ,
          MAX(ITEMS) AS ITEM
      FROM
          SOURCE  
      GROUP BY
          INDEX ,
          DAY  
      UNION
      ALL SELECT
          INDEX ,
          (CASE  
              WHEN ACTIVITY_DAY&gt;=TO_DATE( :year||'-01-01',
              'yyyy-mm-dd' )  
              AND ACTIVITY_DAY&lt;=TO_DATE( :end_day,
              'yyyy-mm-dd' ) THEN 'YTD'  
              ELSE NULL  
          END) AS TIME_PERIOD,
          MAX(ITEMS) AS ITEM
      FROM
          SOURCE  
      GROUP BY
          INDEX ,
          DAY)
  GROUP BY INDEX, TIME_PERIOD  
  </code></pre>
  
  <p>Is there a better way in Oracle?</p>