Seperate records based on time

[daNewfie]

I have a database that logs calls comming in to the call center..

What I need to be able to do is seperate the calls based on time

example...right now it just list the number of calls for the day as one number lets say 50 calls for today in total

I want to be able to break it down and say

Morning calls 8:01 am - 4:00 pm 15 calls
Evening calls 4:01 pm - 12:00 am 25 calls
Night calls 12:01 am - 8:00 am 10 calls

I have no idea how to do this...any help will be appriciated

 

[ksutphin]

This query should bucket all calls with one query. Then just loop through the query results.
<cfquery name="qryCallsbyShift" datasource="mydatsource">
Select 'Morning Calls' as CallTime,
Count(Calls) as CallCnt
from CallLog
Where Convert(varchar,CallTimeDateTime,108) between '08:01:00' and '16:00:59'
Union All
Select 'Evening Calls' as CallTime,
Count(Calls) as CallCnt
from CallLog
Where Convert(varchar,CallTimeDateTime,108) between '16:01:00' and '23:59:59'
Union All
Select 'Night Calls' as CallTime,
Count(Calls) as CallCnt
from CallLog
Where Convert(varchar,CallTimeDateTime,108) between '00:00:00' and '08:00:59'
</cfquery>