Sum grouped by day of week

[OKCMikeSoll]

Hello everyone,

I'm trying to report on the number of calls that arrive by interval for each day of the week. The data is stored by interval so I was trying to group by {table.interval}. I need the columns to be a sum for all calls for that day of week over any given period of time(i.e. last 90 days). The problem is the formula to roll up Mondays, Tuesdays, etc.. Anyone have an idea on how to do this? Thanks in advance.

Mon Tues Wed Thur Fri Sat Sun
0:30 34 50 55 56 22 12 3
1:00 40 61 22 33 23 13 5
1:30
2:00


CR 2008
XCELSIUS 2008
Oracle 9i

 

[OKCMikeSoll]

I figured it out just after I posted. The answer was right in front of me and very simple.

Here's what I did:
if dayofweek({hvdn.row_date}) = 1 then "Sunday" else
if dayofweek({hvdn.row_date}) = 2 then "Monday" else
if dayofweek({hvdn.row_date}) = 3 then "Tuesday" else
if dayofweek({hvdn.row_date}) = 4 then "Wednesday" else
if dayofweek({hvdn.row_date}) = 5 then "Thursday" else
if dayofweek({hvdn.row_date}) = 6 then "Friday" else
if dayofweek({hvdn.row_date}) = 7 then "Saturday"

Duh!! =)

 

[lbass]

You could have used:

weekdayname(dayofweek({hvdn.row_date}))

...as your crosstab column field.

-LB

 

[OKCMikeSoll]

even better.. =) thank you LB