Charles Hooper's Oracle Notes
Miscellaneous Random Oracle Topics: Stop, Think, Understand
Grouping Data Sets by Week Number of
the Month
May 1, 2013
I saw a decent SQL brain teaser this morning in the
comp.databases.oracle.server Usenet group
(https://groups.google.com/forum/?fromgroups#!
topic/comp.databases.oracle.server/N6e5rt6FERs). The OP in the message
thread is attempting to summarize data in one of his tables, with the
summarizations broken down by month and then the week within that
month. Increasing the challenge, the OP required that the dates defining the
week start and end dates fall within the months data set:
Today is 4/30/2013. The we_end [last day of the week containing April 30,
2013] 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
How would you solve that particular problem? Is there an Oracle built-in
function that provides the grouping requested by the OP?
-
-
-
-
There are a couple different methods to solve the problem posed by the OP.
One method uses a virtual lookup table to determine the start date and end
date of each week in the month, using the restriction that the start date of
each week or the end date of each week might need to be adjusted to fall
within the same month. Interestingly, with the restrictions specified by the
OP, there are 62 weeks this year.
As with all SQL statements, there is must be a sensible starting point. First,
lets determine the start and end dates of each month in 2013. The month
start date is easy to determine, while outputting the month end date is a little
more challenging that date is one day less than the start of the next month:
1 05 2013
Page 1 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
SELECT
ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEVEL-1)) MO
ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),LEVEL)-1 MONT
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
There are a couple of approaches that could be taken for the next step, finding
the start of the first, second, third, fourth, fifth, and potentially sixth week in
each of those months. I am interested in determining the start of the second
week we are able to accomplish that task by using the NEXT_DAY function
to find the next Sunday after the first 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 problems depending on the NLS settings on the
client (and whether or not Sunday is considered the first day of the week):
Page 2 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
SELECT
ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEVEL-1)) MO
ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),LEVEL)-1 MONT
NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEV
NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEV
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
The above may appear to be a number of related, but useless dates. However,
that is not the case. Next, we will need a method to count through as many as
six weeks per month. This should work:
SELECT
LEVEL WEEK_NUMBER
FROM
DUAL
CONNECT BY
LEVEL<=6;
WEEK_NUMBER
-----------
1
2
3
4
5
6
Next, a Cartesian join will be created between the two row sources to permit
stepping through each month, and each week within that month (we will
refine the output later note that this unrefined output provides a clue for a
second method of solving the problem posed by the OP):
Page 3 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
SELECT
MONTHS.MONTH_START,
WN.WEEK_NUMBER,
MONTHS.START_SECOND_WEEK + (WN.WEEK_NUMBER-2)*7 START_WEEK,
MONTHS.START_SECOND_WEEK + (WN.WEEK_NUMBER-1)*7 - 1 END_WEE
FROM
(SELECT
ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEVEL-1)) M
ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),LEVEL)-1 MO
NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(L
NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(L
FROM
DUAL
CONNECT BY
LEVEL<=12) MONTHS,
(SELECT
LEVEL WEEK_NUMBER
FROM
DUAL
CONNECT BY
LEVEL<=6) WN
WHERE
(MONTHS.START_SECOND_WEEK + (WN.WEEK_NUMBER-2)*7)<=MONTHS.M
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
Page 4 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
01-JUN-13 1 26-MAY-13 01-JUN-13
...
The above output has a slight problem the Sunday in the first week of the
month may not be in the month of interest; additionally, the Saturday in the
last week of the month may not be in the month of interest. We may
overcome those problems with the help of the DECODE function (the CASE
(http://docs.oracle.com/cd/E11882_01/server.112/e26088/expressions004.htm)
expression may be used instead, if so desired):
Page 5 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
SELECT
MONTHS.MONTH_START,
WN.WEEK_NUMBER,
DECODE(WN.WEEK_NUMBER, 1, MONTHS.MONTH_START, MONTHS.START_
DECODE(SIGN(MONTHS.MONTH_END - (MONTHS.START_SECOND_WEEK + (
FROM
(SELECT
ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(LEVEL-1)) M
ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),LEVEL)-1 MO
NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(L
NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2013','MM-DD-YYYY'),(
FROM
DUAL
CONNECT BY
LEVEL<=12) MONTHS,
(SELECT
LEVEL WEEK_NUMBER
FROM
DUAL
CONNECT BY
LEVEL<=6) WN
WHERE
(MONTHS.START_SECOND_WEEK + (WN.WEEK_NUMBER-2)*7)<=MONTHS.M
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
Page 6 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
...
01-DEC-13 5 29-DEC-13 31-DEC-13
62 rows selected.
Yes, this solution added 10 extra weeks to the year.
It is easy to extend this solution to cover more than just the year 2013. For
example, the following modification sets the starting period to January 2000,
and runs for 20 years:
SELECT
MONTHS.MONTH_START,
WN.WEEK_NUMBER,
DECODE(WN.WEEK_NUMBER, 1, MONTHS.MONTH_START, MONTHS.START_
DECODE(SIGN(MONTHS.MONTH_END - (MONTHS.START_SECOND_WEEK + (
FROM
(SELECT
ADD_MONTHS(TO_DATE('01-01-2000','MM-DD-YYYY'),(LEVEL-1)) M
ADD_MONTHS(TO_DATE('01-01-2000','MM-DD-YYYY'),LEVEL)-1 MO
NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2000','MM-DD-YYYY'),(L
NEXT_DAY(ADD_MONTHS(TO_DATE('01-01-2000','MM-DD-YYYY'),(
FROM
DUAL
CONNECT BY
LEVEL<=(12*20)) MONTHS,
(SELECT
LEVEL WEEK_NUMBER
FROM
DUAL
CONNECT BY
LEVEL<=6) WN
WHERE
(MONTHS.START_SECOND_WEEK + (WN.WEEK_NUMBER-2)*7)<=MONTHS.M
With the virtual lookup table complete, the next step would be to slide the
above SQL statement into an inline view, and then join it to the data set that
will be analyzed. Assuming that the data set has a column named
DATE_SENT, the WHERE clause may appear similar to the following:
WHERE
DATE_SENT BETWEEN START_WEEK AND END_WEEK
The above is one solution, are there other solutions? What about a solution
that uses a little simple mathematics? Such a solution could be exponentially
more efficient. First, we will create a simple table for experimentation with
1000 rows containing somewhat random dates on or after January 1, 2013:
Page 7 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
CREATE TABLE T1 AS
SELECT
TRUNC(TO_DATE('01-01-2013','MM-DD-YYYY')+DBMS_RANDOM.VALUE*
FROM
DUAL
CONNECT BY
LEVEL<=1000;
Next, we will generate a SQL statement that produces output that is
somewhat similar to the unrefined output found in the first solution (note that
the SQL statement contains a DISTINCT clause to eliminate duplicate rows
there could be several rows in the row source with the same random date
value):
Page 8 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
SELECT DISTINCT
DATE_SENT,
TRUNC(DATE_SENT,'MM') MONTH_START,
ADD_MONTHS(TRUNC(DATE_SENT,'MM'),1)-1 MONTH_END,
NEXT_DAY(TRUNC(DATE_SENT,'MM')-7,'SUNDAY') MONTH_WEEK_START
FROM
T1
ORDER BY
DATE_SENT;
DATE_SENT MONTH_STA MONTH_END MONTH_WEE
--------- --------- --------- ---------
01-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
02-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
03-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
04-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
05-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
06-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
07-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
08-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
09-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
10-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
11-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
12-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
13-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
14-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
15-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
16-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
17-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
18-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
19-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
20-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
21-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
22-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
23-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
24-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
25-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
26-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
27-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
28-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
29-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
30-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
31-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12
01-FEB-13 01-FEB-13 28-FEB-13 27-JAN-13
02-FEB-13 01-FEB-13 28-FEB-13 27-JAN-13
03-FEB-13 01-FEB-13 28-FEB-13 27-JAN-13
...
29-DEC-13 01-DEC-13 31-DEC-13 01-DEC-13
30-DEC-13 01-DEC-13 31-DEC-13 01-DEC-13
31-DEC-13 01-DEC-13 31-DEC-13 01-DEC-13
01-JAN-14 01-JAN-14 31-JAN-14 29-DEC-13
Page 9 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
In the above, the MONTH_WEEK_START column shows the Sunday of the
week that includes the DATE_SENT column value. At this point, it makes no
difference if the MONTH_WEEK_START column value is in a different
month than the DATE_SENT column value.
Next, we will determine the week within the month in which the
DATE_SENT column value appears:
Page 10 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
SELECT DISTINCT
DATE_SENT,
TRUNC(DATE_SENT,'MM') MONTH_START,
ADD_MONTHS(TRUNC(DATE_SENT,'MM'),1)-1 MONTH_END,
NEXT_DAY(TRUNC(DATE_SENT,'MM')-7,'SUNDAY') MONTH_WEEK_START
TRUNC((DATE_SENT-(NEXT_DAY(TRUNC(DATE_SENT,'MM')-7,'SUNDAY'
FROM
T1
ORDER BY
DATE_SENT;
DATE_SENT MONTH_STA MONTH_END MONTH_WEE WEEK_NUMBER
--------- --------- --------- --------- -----------
01-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 1
02-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 1
03-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 1
04-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 1
05-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 1
06-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 2
07-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 2
08-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 2
09-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 2
10-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 2
11-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 2
12-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 2
13-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 3
14-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 3
15-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 3
16-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 3
17-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 3
18-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 3
19-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 3
20-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 4
21-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 4
22-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 4
23-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 4
24-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 4
25-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 4
26-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 4
27-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 5
28-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 5
29-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 5
30-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 5
31-JAN-13 01-JAN-13 31-JAN-13 30-DEC-12 5
01-FEB-13 01-FEB-13 28-FEB-13 27-JAN-13 1
02-FEB-13 01-FEB-13 28-FEB-13 27-JAN-13 1
03-FEB-13 01-FEB-13 28-FEB-13 27-JAN-13 2
04-FEB-13 01-FEB-13 28-FEB-13 27-JAN-13 2
...
24-DEC-13 01-DEC-13 31-DEC-13 01-DEC-13 4
26-DEC-13 01-DEC-13 31-DEC-13 01-DEC-13 4
27-DEC-13 01-DEC-13 31-DEC-13 01-DEC-13 4
Page 11 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
28-DEC-13 01-DEC-13 31-DEC-13 01-DEC-13 4
29-DEC-13 01-DEC-13 31-DEC-13 01-DEC-13 5
30-DEC-13 01-DEC-13 31-DEC-13 01-DEC-13 5
31-DEC-13 01-DEC-13 31-DEC-13 01-DEC-13 5
01-JAN-14 01-JAN-14 31-JAN-14 29-DEC-13 1
Heading toward the request made by the OP in the Usenet thread, the
following SQL statement will determine a simple COUNT of the number of
rows that have a DATE_SENT date in each week of each month in the T1
tables data set (note that if there are no rows in a given week, that week will
be skipped in the output):
Page 12 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
SELECT
TRUNC(DATE_SENT,'MM') MONTH_START,
TRUNC((DATE_SENT-(NEXT_DAY(TRUNC(DATE_SENT,'MM')-7,'SUNDAY'
COUNT(*) DAYS_IN_SET
FROM
T1
GROUP BY
TRUNC(DATE_SENT,'MM'),
TRUNC((DATE_SENT-(NEXT_DAY(TRUNC(DATE_SENT,'MM')-7,'SUNDAY'
ORDER BY
1,
2;
MONTH_STA WEEK_NUMBER DAYS_IN_SET
--------- ----------- -----------
01-JAN-13 1 13
01-JAN-13 2 15
01-JAN-13 3 23
01-JAN-13 4 21
01-JAN-13 5 13
01-FEB-13 1 5
01-FEB-13 2 21
01-FEB-13 3 15
01-FEB-13 4 14
01-FEB-13 5 11
01-MAR-13 1 4
01-MAR-13 2 18
01-MAR-13 3 19
01-MAR-13 4 20
01-MAR-13 5 20
01-MAR-13 6 4
01-APR-13 1 14
01-APR-13 2 26
01-APR-13 3 15
01-APR-13 4 23
01-APR-13 5 9
01-MAY-13 1 12
01-MAY-13 2 15
01-MAY-13 3 22
01-MAY-13 4 23
01-MAY-13 5 17
01-JUN-13 1 1
01-JUN-13 2 14
01-JUN-13 3 21
01-JUN-13 4 18
01-JUN-13 5 21
01-JUN-13 6 4
01-JUL-13 1 15
01-JUL-13 2 21
01-JUL-13 3 13
01-JUL-13 4 24
01-JUL-13 5 14
Page 13 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
01-AUG-13 1 6
01-AUG-13 2 10
01-AUG-13 3 21
01-AUG-13 4 22
01-AUG-13 5 28
01-SEP-13 1 16
01-SEP-13 2 16
01-SEP-13 3 22
01-SEP-13 4 23
01-SEP-13 5 8
01-OCT-13 1 15
01-OCT-13 2 15
01-OCT-13 3 17
01-OCT-13 4 18
01-OCT-13 5 21
01-NOV-13 1 6
01-NOV-13 2 20
01-NOV-13 3 20
01-NOV-13 4 17
01-NOV-13 5 18
01-DEC-13 1 22
01-DEC-13 2 15
01-DEC-13 3 19
01-DEC-13 4 20
01-DEC-13 5 6
01-JAN-14 1 1
It might be just my opinion, but the mathematical solution appears to be
easier to understand than the method that uses the virtual lookup table. We
are still able to derive the start date and end date of each week, if needed, to
produce the OPs solution.
Might there be other solutions to the OPs SQL brain teaser?
Actions
Comments RSS
Trackback
Information
Date : May 1, 2013
Categories : SQL
Page 14 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
2 responses
Latest data Industry news round up, Log Buffer #318 (07:51:03) :
[...] Charles Hooper is grouping Data Sets by Week Number of the Month.
[...]
Reply
Latest data Industry news round up, Log Buffer #319 (08:52:40) :
[] Grouping Data Sets by Week Number of the Month []
Reply
Blog at WordPress.com. The Freshy Theme.
Follow
Follow Charles Hooper's Oracle Notes
Powered by WordPress.com
3 05 2013
10 05 2013
Page 15 of 15 Grouping Data Sets by Week Number of the Month | Charles Hooper's Oracle Notes
22/01/2014 http://hoopercharles.wordpress.com/2013/05/01/grouping-data-sets-by-week-number-in-the-mon...
