# 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?

LVL 17
###### Who is Participating?

Commented:
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
``````
0

progCommented:
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

Commented:
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
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.