Re: Grouping by Week in Month

From: <jpbard_at_gmail.com>
Date: Fri, 3 May 2013 10:10:19 -0700 (PDT)
Message-ID: <4d34a292-43d3-45b8-ac89-a30a92dd8059_at_googlegroups.com>

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. Received on Fri May 03 2013 - 19:10:19 CEST