# Re: Grouping by Week in Month

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 2 May 2013 12:30:04 -0700 (PDT)

On Thursday, May 2, 2013 2:47:12 PM UTC-4, artm..._at_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).

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu May 02 2013 - 21:30:04 CEST

Original text of this message