Sorting weekdays

building reports desktopintelligence
1

Something strange is going on with weekdays/-numbers when displaying them in a x-tab.
I have a report show week’s (weeknumbers) in the rows and weekdays in the columns. Oracle7.3 apperently things that a week starts with monday, BO’s week starts at sunday. This leaves for ongoing data the first cell “sunday” of the current week empty, until the “end” of the week. Is there an elegant solution to this?

han

************************************************************* Hans Kingma
Unisource Carrier Services
Industriestrasse 21
8304 Wallisellen
Switzerland
Phone: +41 1 839 32 11
Fax: +41 1 839 32 95

Listserv Archives (BOB member since 2002-06-25)

2

Hans,
I think that the days of the week should have sorted in alphanumeric by default, therefore Monday would be first. You can fix this by highlighting the column with the days, then select Format, Sort. Choose Add, then highlight the field that has your weekday in it, press the OK button which will bring you back to the sort screen. Once again highlight the added field with week day and on the right hand side of the screen there is an option to perform a “Custom” sort. Click on Custom, then select the “Values” button. Notice that your weekdays will be in a list. You simply need to highlight Sunday and click on the “Move Up” button until Sunday appears at the top of the list.
This should fix the problem, click "OK’, and then “Apply”. You are good to go! laurenf@bellatlantic.net
hans.kingma@UNISOURCE-UCS.COM wrote:

Something strange is going on with weekdays/-numbers when displaying them in a x-tab.
I have a report show week’s (weeknumbers) in the rows and weekdays in the columns. Oracle7.3 apperently things that a week starts with monday, BO’s week starts at sunday. This leaves for ongoing data the first cell “sunday” of the current week empty, until the “end” of the week. Is there an elegant solution to this?

han

************************************************************* Hans Kingma
Unisource Carrier Services
Industriestrasse 21
8304 Wallisellen
Switzerland
Phone: +41 1 839 32 11
Fax: +41 1 839 32 95

OR search: Mail to listserv@listserv.aol.com, ‘search a_phrase in BUSOB-L’ Unsubscribe: Mail to listserv@listserv.aol.com, ‘unsubscribe BUSOB-L’

Listserv Archives (BOB member since 2002-06-25)

3

Thank you Lauren for your reply. Indeed the sorting of BO for months of weekdays is weak. But in this case, I;ve discoverd not BO is to blame but Oracle. Week 43 ( this week ) contains dates 25.oct to 31 Oct, so starting with a monday. But when assigning weekday numbers 25.Oct (sunday) gets a 7, 26Oct. 1, 27Oct. 2 … 31Oct. a 6. So when displaing current weeks it always leaves a “hole” for the first cell (sunday) until the week is complete. Solution is of course defining the sort.

Listserv Archives (BOB member since 2002-06-25)

4

Hans,

I suspect the confusion is not how Oracle determines the day of the week, but how Oracle determines the week of the year. In MY Oracle database anyway, the days of the week are what you would expect, Sunday=1, Monday=2, etc. (Maybe you have some parameter set in your database to change that…)

However, the beginning of a “week” is determined by THE FIRST DAY OF THE YEAR; it is not “Sunday” by default. This year 1998 started on a Thursday, so “week 43” started on Thursday, Oct. 22nd, not Sunday, Oct. 25th as you assumed.

Look at this query:

select sysdate, to_char(sysdate,‘D’), to_char(sysdate,‘WW’) from dual union all
select sysdate+1, to_char(sysdate+1,‘D’), to_char(sysdate+1,‘WW’) from dual union all
select sysdate+2, to_char(sysdate+2,‘D’), to_char(sysdate+2,‘WW’) from dual union all
select sysdate+3, to_char(sysdate+3,‘D’), to_char(sysdate+3,‘WW’) from dual union all
select sysdate+4, to_char(sysdate+4,‘D’), to_char(sysdate+4,‘WW’) from dual union all
select sysdate+5, to_char(sysdate+5,‘D’), to_char(sysdate+5,‘WW’) from dual union all
select sysdate+6, to_char(sysdate+6,‘D’), to_char(sysdate+6,‘WW’) from dual union all
select sysdate+7, to_char(sysdate+7,‘D’), to_char(sysdate+7,‘WW’) from dual union all
select sysdate+8, to_char(sysdate+8,‘D’), to_char(sysdate+8,‘WW’) from dual

On my database, it returns this:

SYSDATE TO_CHAR(SYSDATE,‘D’) TO_CHAR(SYSDATE,‘WW’)

Listserv Archives (BOB member since 2002-06-25)

5

Thanks Erich for your elaborate explanation. It indeed is a handling of weeknos. in a non standard way by Oracle. Week 2 of this year starts with sunday 4-jan-98, doesn’t it? By the way I’m using the ‘IW’ iso - standard week format because that is used in every calendar as well ( my Psion 3a has a weeknumbering scheme of it;s own starting with week 53 of the previous year).
but try this:

select
to_char(to_date(‘01-jan’,‘dd-mon’),‘IW’),to_char(to_date(‘01-jan’,‘dd-mo n’),‘d’) from dual
union all
select
to_char(to_date(‘04-jan’,‘dd-mon’),‘IW’),to_char(to_date(‘04-jan’,‘dd-mo n’),‘d’)
from dual

and you see that when putting weekno’s in rows and weekday’s in column’s you can see that you get a later date (4/1) put prior to 1/1 which confuses the users of my reports and me.

hans

Listserv Archives (BOB member since 2002-06-25)

6

Hi Hans,

I always make this an object and use a decode to get the sunday daynumber within the week right:

Decode(to_char(,‘d’),‘1’,‘7’,to_char(,‘d’)-1)

this way monday=1,tuesday=2,…sunday=7. Which you could consider to be the ISO day number, since the ISO week starts on a monday. Then users are free to select and use it for sorting purposes.

Greetings,
Marianne Wagt
IDETA
The Netherlands

Listserv Archives (BOB member since 2002-06-25)