Re: Grouping by Week in Month
Date: Wed, 1 May 2013 04:30:23 -0700 (PDT)
Message-ID: <b9c1ff53-88a6-4ec2-8fcb-def27f4831bf_at_googlegroups.com>
On Tuesday, April 30, 2013 10:32:32 AM UTC-4, artm..._at_gmail.com wrote:
> 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!
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.
Received on Wed May 01 2013 - 13:30:23 CEST