Grouping by Week in Month

From: <artmerar_at_gmail.com>
Date: Tue, 30 Apr 2013 07:32:32 -0700 (PDT)
Message-ID: <ad63f592-da20-49e9-b8b0-40f4b3752c78_at_googlegroups.com>

Hi,

I am using this query to sum data for each week in the month:

SELECT product_description, wk_start, wk_end, accepted, total FROM (SELECT service_id, TRUNC(date_sent+1,'IW')-1 wk_start, TRUNC(date_sent+1,'IW')+5 wk_end, SUM(accepted) accepted, SUM(list_count) total

          FROM trading_service_stats
          WHERE date_sent BETWEEN TRUNC(SYSDATE,'MM') AND 
                                  TRUNC(LAST_DAY(SYSDATE)+1)
          GROUP BY service_id, TRUNC(date_sent+1,'IW')) a, email_products_vw e

WHERE a.service_id = e.email_product_id
ORDER BY wk_start, product_description;

Today is 4/30/2013. The we_end value is 5/4/2013. What I am really looking for is the weeks within the calendar month.

So, the first week for April 2013 should show 4/1 - 4/6. And the last week should show 4/28 - 4/30.

Can anyone help me expand on this to get the date columns to actually be the week date ranges for the actual month?

Thanks! Received on Tue Apr 30 2013 - 16:32:32 CEST