Grouping by Week in Month

artm...@gmail.com

unread,

Apr 30, 2013, 10:32:32 AM4/30/13

to

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!

Charles Hooper

unread,

May 1, 2013, 7:30:23 AM5/1/13

to

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.

artm...@gmail.com

unread,

May 2, 2013, 2:47:12 PM5/2/13

to

I got 2 words.....WOW & WOW!!

Very nicely done!

Charles Hooper

unread,

May 2, 2013, 3:30:04 PM5/2/13

to

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).

jpb...@gmail.com

unread,

May 3, 2013, 1:10:19 PM5/3/13

to

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.