Grouping by weekday
244101Jan 16 2008 — edited Feb 6 2008Hi,
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