Re: Grouping by Week in Month

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 2 May 2013 12:30:04 -0700 (PDT)
Message-ID: <ad74ad6c-0d5f-4bce-9b3c-28785f9fa899_at_googlegroups.com>

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