| Author |
Message |
ricky969
Groupie
Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
|
  
Topic: totals by day of week and hours
Posted: 08 Jun 2011 at 11:22am |
guys i need your help. i need to create a report with graph showing the total number of calls for each day of the week and by time
mon tue wed thu fri sat sun
0 0 0 1 0 0 0 0
1 0 0 1 0 0 0 0
2 0 0 1 0 0 0 0
3 0 0 1 0 0 0 0
4 5 7 0 3 4 2 1
.
.
20 10 15 7 8 9 20 3
21 12
22 8
23
i have a field that returns datetime
thanks
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Jun 2011 at 11:53am |
possibly ...
use 2 formula to get the daya of week and the time
weekday formula//
totext(weekday(datetimefield),0,"")+"-"+weekdayname(weekday(datetimefield))
Hour formula//
Hour(datetimefield)
Insert a Crosstab
Column kis day of week formula
Row is Hour formula
Summarized field is a distinctcount of a PrimaryKey field Edited by DBlank - 08 Jun 2011 at 11:53am
|
| IP Logged |
|
ricky969
Groupie
Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
|
Posted: 08 Jun 2011 at 1:26pm |
DBlank
thank you very much for the quick and accurate solution. It worked perfectly.
|
| IP Logged |
|
ricky969
Groupie
Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
|
Posted: 08 Jun 2011 at 3:22pm |
i have an additional question...whats the best way to display all 24 hours on the cross tab regardless if there is a record that falls into a specific time slot or not. for example if i don't have any record between 1:01am and 2:00 am the cross tab will show: 0, 2, 3, 4, etc.
i need to display all 24 time slots.
thank you
|
| IP Logged |
|
CircleD
Senior Member
Joined: 11 Mar 2011
Location: United States
Online Status: Offline
Posts: 251
|
Posted: 08 Jun 2011 at 6:02pm |
|
I may be blowing hot air but would something like If isnull then " " work?
|
| IP Logged |
|
ricky969
Groupie
Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
|
Posted: 08 Jun 2011 at 6:19pm |
i don't think so. i believe that in a previous report i had used a custom function but i cannot remember how i did it.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 09 Jun 2011 at 3:48am |
|
you need a second source table that has all of your values and then you outer join your other table to it and do the grouping on the source table with all values
|
| IP Logged |
|
ricky969
Groupie
Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
|
Posted: 09 Jun 2011 at 5:10am |
i tried WITH hours AS ( SELECT 1 AS hour UNION SELECT 2 AS hour ... UNION SELECT 24 AS hour ) SELECT * FROM hours h LEFT OUTER JOIN... this does not work on my sql server 2000 :-( i thought about creating a table on the DB but that's not a nice thing to do. is there any other way other than using WITH?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 09 Jun 2011 at 5:34am |
|
maybe a stored proc using a temp table?
|
| IP Logged |
|
ricky969
Groupie
Joined: 03 Jul 2008
Location: United States
Online Status: Offline
Posts: 53
|
Posted: 09 Jun 2011 at 4:53pm |
I wrote a stored proc and got the results i needed...thank you for your help and suggestion.
|
| IP Logged |
|
|
|
