Grouping by Week in Month

Showing 1-5 of 5 messages
Grouping by Week in Month artm...@gmail.com 4/30/13 7:32 AM
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!
Re: Grouping by Week in Month Charles Hooper 5/1/13 4:30 AM
That looks like an interesting problem - there are 62 weeks this year.  :-)

There are probably a couple of different approaches to this problem - one method would be to use a virtual lookup table to determine the week start and end dates per month.

Let's first find the start and end dates of each month of 2013.  The month start date is easy to determine, whil ethe month end date is one day less than the start of the next month:
SELECT
  ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEVEL-1)) MONTH_START,
  ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),LEVEL)-1 MONTH_END
FROM
  DUAL
CONNECT BY
  LEVEL<=12;
 
MONTH_STA MONTH_END
--------- ---------
01-JAN-13 31-JAN-13
01-FEB-13 28-FEB-13
01-MAR-13 31-MAR-13
01-APR-13 30-APR-13
01-MAY-13 31-MAY-13
01-JUN-13 30-JUN-13
01-JUL-13 31-JUL-13
01-AUG-13 31-AUG-13
01-SEP-13 30-SEP-13
01-OCT-13 31-OCT-13
01-NOV-13 30-NOV-13
01-DEC-13 31-DEC-13

Next, we should determine the start of the second week - we are able to accomplish that with the NEXT_DAY function to find the next Sunday after the start of the month.  The end of the first week will also be calculated, even though it is not used in later calculations.  Note that this solution is subject to the NLS settings on the client:
SELECT
  ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEVEL-1)) MONTH_START,
  ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),LEVEL)-1 MONTH_END,
  NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEVEL-1))-1,'SATURDAY') END_FIRST_WEEK,
  NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEVEL-1)),'SUNDAY') START_SECOND_WEEK
FROM
  DUAL
CONNECT BY
  LEVEL<=12;
 
MONTH_STA MONTH_END END_FIRST START_SEC
--------- --------- --------- ---------
01-JAN-13 31-JAN-13 05-JAN-13 06-JAN-13
01-FEB-13 28-FEB-13 02-FEB-13 03-FEB-13
01-MAR-13 31-MAR-13 02-MAR-13 03-MAR-13
01-APR-13 30-APR-13 06-APR-13 07-APR-13
01-MAY-13 31-MAY-13 04-MAY-13 05-MAY-13
01-JUN-13 30-JUN-13 01-JUN-13 02-JUN-13
01-JUL-13 31-JUL-13 06-JUL-13 07-JUL-13
01-AUG-13 31-AUG-13 03-AUG-13 04-AUG-13
01-SEP-13 30-SEP-13 07-SEP-13 08-SEP-13
01-OCT-13 31-OCT-13 05-OCT-13 06-OCT-13
01-NOV-13 30-NOV-13 02-NOV-13 03-NOV-13
01-DEC-13 31-DEC-13 07-DEC-13 08-DEC-13

Next we will need to be able to step through the weeks in each month, potentially 6 weeks per month:
SELECT
  LEVEL WEEK_NUMBER
FROM
  DUAL
CONNECT BY
  LEVEL<=6;
 
WEEK_NUMBER
-----------
          1
          2
          3
          4
          5
          6

Next, we will create a Cartesian join between the two row sources (we will refine the output later):
SELECT
  MONTH_START,
  WN.WEEK_NUMBER,
  START_SECOND_WEEK + (WN.WEEK_NUMBER-2)*7 START_WEEK,
  START_SECOND_WEEK + (WN.WEEK_NUMBER-1)*7 - 1 END_WEEK
FROM
  (SELECT
    ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEVEL-1)) MONTH_START,
    ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),LEVEL)-1 MONTH_END,
    NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEVEL-1))-1,'SATURDAY') END_FIRST_WEEK,
    NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEVEL-1)),'SUNDAY') START_SECOND_WEEK
  FROM
    DUAL
  CONNECT BY
    LEVEL<=12) MONTHS,
  (SELECT
    LEVEL WEEK_NUMBER
  FROM
    DUAL
  CONNECT BY
    LEVEL<=6) WN
WHERE
  (START_SECOND_WEEK + (WN.WEEK_NUMBER-2)*7)<=MONTHS.MONTH_END;
 
MONTH_STA WEEK_NUMBER START_WEE END_WEEK
--------- ----------- --------- ---------
01-JAN-13           1 30-DEC-12 05-JAN-13
01-JAN-13           2 06-JAN-13 12-JAN-13
01-JAN-13           3 13-JAN-13 19-JAN-13
01-JAN-13           4 20-JAN-13 26-JAN-13
01-JAN-13           5 27-JAN-13 02-FEB-13
01-FEB-13           1 27-JAN-13 02-FEB-13
01-FEB-13           2 03-FEB-13 09-FEB-13
01-FEB-13           3 10-FEB-13 16-FEB-13
01-FEB-13           4 17-FEB-13 23-FEB-13
01-FEB-13           5 24-FEB-13 02-MAR-13
01-MAR-13           1 24-FEB-13 02-MAR-13
01-MAR-13           2 03-MAR-13 09-MAR-13
01-MAR-13           3 10-MAR-13 16-MAR-13
01-MAR-13           4 17-MAR-13 23-MAR-13
01-MAR-13           5 24-MAR-13 30-MAR-13
01-MAR-13           6 31-MAR-13 06-APR-13
01-APR-13           1 31-MAR-13 06-APR-13
01-APR-13           2 07-APR-13 13-APR-13
01-APR-13           3 14-APR-13 20-APR-13
01-APR-13           4 21-APR-13 27-APR-13
01-APR-13           5 28-APR-13 04-MAY-13
01-MAY-13           1 28-APR-13 04-MAY-13
01-MAY-13           2 05-MAY-13 11-MAY-13
01-MAY-13           3 12-MAY-13 18-MAY-13
01-MAY-13           4 19-MAY-13 25-MAY-13
01-MAY-13           5 26-MAY-13 01-JUN-13
01-JUN-13           1 26-MAY-13 01-JUN-13
...

Next, we need to make certain that the end of the week is still in the current month, and that the start of the week is also in the current month.  I will accomplish that task with the DECODE function:
SELECT
  MONTH_START,
  WN.WEEK_NUMBER,
  DECODE(WEEK_NUMBER,1,MONTH_START,START_SECOND_WEEK + (WN.WEEK_NUMBER-2)*7) START_WEEK,
  DECODE(SIGN(MONTH_END-(START_SECOND_WEEK + (WN.WEEK_NUMBER-1)*7)),-1,MONTH_END,START_SECOND_WEEK + (WN.WEEK_NUMBER-1)*7 - 1) END_WEEK
FROM
  (SELECT
    ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEVEL-1)) MONTH_START,
    ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),LEVEL)-1 MONTH_END,
    NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEVEL-1))-1,'SATURDAY') END_FIRST_WEEK,
    NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEVEL-1)),'SUNDAY') START_SECOND_WEEK
  FROM
    DUAL
  CONNECT BY
    LEVEL<=12) MONTHS,
  (SELECT
    LEVEL WEEK_NUMBER
  FROM
    DUAL
  CONNECT BY
    LEVEL<=6) WN
WHERE
  (START_SECOND_WEEK + (WN.WEEK_NUMBER-2)*7)<=MONTHS.MONTH_END;
 
MONTH_STA WEEK_NUMBER START_WEE END_WEEK
--------- ----------- --------- ---------
01-JAN-13           1 01-JAN-13 05-JAN-13
01-JAN-13           2 06-JAN-13 12-JAN-13
01-JAN-13           3 13-JAN-13 19-JAN-13
01-JAN-13           4 20-JAN-13 26-JAN-13
01-JAN-13           5 27-JAN-13 31-JAN-13
01-FEB-13           1 01-FEB-13 02-FEB-13
01-FEB-13           2 03-FEB-13 09-FEB-13
01-FEB-13           3 10-FEB-13 16-FEB-13
01-FEB-13           4 17-FEB-13 23-FEB-13
01-FEB-13           5 24-FEB-13 28-FEB-13
01-MAR-13           1 01-MAR-13 02-MAR-13
01-MAR-13           2 03-MAR-13 09-MAR-13
01-MAR-13           3 10-MAR-13 16-MAR-13
01-MAR-13           4 17-MAR-13 23-MAR-13
01-MAR-13           5 24-MAR-13 30-MAR-13
01-MAR-13           6 31-MAR-13 31-MAR-13
01-APR-13           1 01-APR-13 06-APR-13
01-APR-13           2 07-APR-13 13-APR-13
01-APR-13           3 14-APR-13 20-APR-13
01-APR-13           4 21-APR-13 27-APR-13
01-APR-13           5 28-APR-13 30-APR-13
01-MAY-13           1 01-MAY-13 04-MAY-13
01-MAY-13           2 05-MAY-13 11-MAY-13
01-MAY-13           3 12-MAY-13 18-MAY-13
01-MAY-13           4 19-MAY-13 25-MAY-13
01-MAY-13           5 26-MAY-13 31-MAY-13
...
01-DEC-13           5 29-DEC-13 31-DEC-13
 
62 rows selected.

62 weeks in the year.

We probably need to account for more than just 2013.  The following modification will handle 20 years starting with the year 2000:
SELECT
  MONTH_START,
  WN.WEEK_NUMBER,
  DECODE(WEEK_NUMBER,1,MONTH_START,START_SECOND_WEEK + (WN.WEEK_NUMBER-2)*7) START_WEEK,
  DECODE(SIGN(MONTH_END-(START_SECOND_WEEK + (WN.WEEK_NUMBER-1)*7)),-1,MONTH_END,START_SECOND_WEEK + (WN.WEEK_NUMBER-1)*7 - 1) END_WEEK
FROM
  (SELECT
    ADD_MONTHS(TO_DATE('01-01-2000','MM-DD-YYYY'),(LEVEL-1)) MONTH_START,
    ADD_MONTHS(TO_DATE('01-01-2000','MM-DD-YYYY'),LEVEL)-1 MONTH_END,
    NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2000','MM-DD-YYYY'),(LEVEL-1))-1,'SATURDAY') END_FIRST_WEEK,
    NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2000','MM-DD-YYYY'),(LEVEL-1)),'SUNDAY') START_SECOND_WEEK
  FROM
    DUAL
  CONNECT BY
    LEVEL<=(12*20)) MONTHS,
  (SELECT
    LEVEL WEEK_NUMBER
  FROM
    DUAL
  CONNECT BY
    LEVEL<=6) WN
WHERE
  (START_SECOND_WEEK + (WN.WEEK_NUMBER-2)*7)<=MONTHS.MONTH_END;

If you were to use the above approach, the next step would be to slide the above into an inline view and join it to your TRADING_SERVICE_STATS table using a BETWEEEN clause:
DATE_SENT BETWEEN START_WEEK AND END_WEEK

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Re: Grouping by Week in Month artm...@gmail.com 5/2/13 11:47 AM
I got 2 words.....WOW & WOW!!

Very nicely done!
Re: Grouping by Week in Month Charles Hooper 5/2/13 12:30 PM
On Thursday, May 2, 2013 2:47:12 PM UTC-4, artm...@gmail.com wrote:
> I got 2 words.....WOW & WOW!!
>
> Very nicely done!

You should also be able to use a little mathematics logic, if you prefer not to use the other method that I showed.  For example, assume that you create a table T1 with 1000 random dates that fall within 366 days of January 1, 2013:
CREATE TABLE T1 AS
SELECT
  TRUNC(TO_DATE('01-01-2013','MM-DD-YYYY')+DBMS_RANDOM.VALUE*366) DATE_SENT
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;

If you want to determine the number of days that fall into each of the weeks within each month, you could do something like this:
SELECT
  TRUNC(DATE_SENT,'MM') MONTH_START,
  TRUNC((DATE_SENT-(NEXT_DAY(TRUNC(DATE_SENT,'MM')-7,'SUNDAY')))/7)+1 WEEK_NUMBER,
  COUNT(*) DAYS_IN_SET
FROM
  T1
GROUP BY
  TRUNC(DATE_SENT,'MM'),
  TRUNC((DATE_SENT-(NEXT_DAY(TRUNC(DATE_SENT,'MM')-7,'SUNDAY')))/7)+1
ORDER BY
  1,
  2;

If you need the week start and end dates also, see the formulas in my previous reply (or see the related article on my blog).
Re: Grouping by Week in Month jpb...@gmail.com 5/3/13 10:10 AM
I would agree that a mathematical solution looks more elegant.

The calendar month start is the first day of the month:
trunc(date_sent, 'MM')

The calendar month end is the last day of the month:
last_day(date_sent)

The week start is the first day of the week or the first day of the month, whichever is later:
greatest(trunc(date_sent, 'D'), trunc(date_sent, 'MM'))

The week end is the last day of the week or the last day of the month, whichever is earlier:
least(trunc(date_sent + 7, 'D') - 1, last_day(date_sent))

The month week number can therefore be calculated:
((trunc(date_sent, 'D') - trunc(trunc(date_sent, 'MM'), 'D')) / 7) + 1

Note: This uses the current NLS settings for week start and end.