Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Grouping by weekday

244101Jan 16 2008 — edited Feb 6 2008
Hi,

How can we show details and group by week number for a set of data when the custom week begins Saturday and ends Friday?
For example, the table has data as follows:
Date Hrs Weekday
12/01/2007 8 Saturday
12/02/2007 8 Sunday
12/03/2007 11 Monday
12/04/2007 0 Tuesday
12/05/2007 9 Wednesday
12/06/2007 8 Thursday
12/07/2007 8 Friday
12/08/2007 8 Saturday
12/09/2007 9 Sunday
12/10/2007 8 Monday
12/11/2007 0 Tuesday
12/12/2007 0 Wednesday
12/13/2007 12 Thursday
12/14/2007 8 Friday
12/15/2007 9 Saturday
...
...

In Oracle Reports, if I group it by week number using
to_char(to_date(:p_date,'DD-MON-RRRR'),'WW') within the query, I get the week no of the year and begins from Monady of the date range.
So it gives my summary like
Week1 16
Week2 53
Week3 37

I wanted the report to show it as
Week1 52 (from 12/01/2007 to 12/07/2007)
Week2 45 (from 12/08/2007 to 12/14/2007)
Week3 9 (for 12/15/2007 and beyond ...

Can someone shed some light on how to achieve it without using any temporary table and within the SQL quey for use in Oracle Reports?
Your valuable inputs is very much appreciated.
Thanks
uds
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 5 2008
Added on Jan 16 2008
6 comments
1,447 views