• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

Get Count By Time Slot

Hi
I have the following raw data
Date         Number Of Calls          Average Wait Time(seconds)
1-Feb-10      2                        7.12
2-Feb-10      5                        10.2
3-Feb-10      20                        19.02
4-Feb-10      2                         2.33
5-Feb-10      1                         0.09
6-Feb-10      6                         1.12
7-Feb-10      8                         8.34

and I wish to get this from the raw data

Wait Time Slot      Number of Calls
< 5 seconds                            9
5 - 10 seconds      10
10 - 15 seconds      5
15 - 20 seconds      20

Has anyone any ideas about something clever I can do with SQL and the RANK() function or something to get the data grouped and totaled by the different Wait Time slots?


0
Barry Cunney
Asked:
Barry Cunney
  • 2
1 Solution
 
deightonCommented:
If the first set of data is raw data, then the breakdown of how that average was achieved is not there, and I can't see how you can do that.

for example on 3rd feb, you need to know ho long each of those 50 calls lasted
0
 
Ephraim WangoyaCommented:
try this
select [Number Of Calls], TimeSlot
from
	(select [Number Of Calls],
		   case 
			 when [Average Wait Time] < 5 then
			   '<6'
			 when [Average Wait Time] < 11 then
			   '6-10'  
			 when [Average Wait Time] < 16 then
			   '11-15'  
			 when [Average Wait Time] < 21 then
			   '16-20'
		   end as TimeSlot
	from  table1) A
group by A.[Number Of Calls], A.TimeSlot

Open in new window

0
 
Ephraim WangoyaCommented:
Include sum
select SUM([Number Of Calls]) as [Number Of Calls], TimeSlot
from
	(select [Number Of Calls],
		   case 
			 when [Average Wait Time] < 5 then
			   '<6'
			 when [Average Wait Time] < 11 then
			   '6-10'  
			 when [Average Wait Time] < 16 then
			   '11-15'  
			 when [Average Wait Time] < 21 then
			   '16-20'
		   end as TimeSlot
	from  table1) A
group by A.[Number Of Calls], A.TimeSlot

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now