SQL count by time intervals

From: <schroeder915_at_yahoo.com>
Date: 9 Nov 2005 10:34:52 -0800
Message-ID: <1131561292.154818.172720@g47g2000cwa.googlegroups.com>

I work for an organization that takes inbound calls. Each call is stamped with a time.

First, I'd like to just get a count of the number of calls that occurred over a date range, grouped by time ranges. The time ranges correspond to 3 work shifts:
7:00AM - 2:59PM
3PM - 10:59PM
11PM - 6:59AM

Once that issue is tackled, I'm going to have to count the types of calls that occurred in each work shift for a specified date range.

Here's a sample of the data (pipe delimited): DATE_TIME_REC|TYPE

11/6/2005 12:06:00 AM|107
11/6/2005 12:06:00 AM|21
11/6/2005 12:09:00 AM|62A
11/6/2005 12:10:00 AM|17
11/6/2005 12:11:00 AM|107
11/6/2005 12:11:00 AM|21
11/6/2005 12:23:00 AM|21
11/6/2005 12:31:00 AM|103
11/6/2005 12:32:00 AM|62A
11/6/2005 12:33:00 AM|107C
11/6/2005 12:34:00 AM|22N
11/6/2005 12:37:00 AM|21
11/6/2005 12:40:00 AM|21
11/6/2005 12:41:00 AM|18
11/6/2005 12:44:00 AM|21
11/6/2005 12:48:00 AM|21
11/6/2005 12:50:00 AM|107C
11/6/2005 12:55:00 AM|98
11/6/2005 12:56:00 AM|18
11/6/2005 12:56:00 AM|98
11/6/2005 12:57:00 AM|966
11/6/2005 12:58:00 AM|62A
11/6/2005 12:59:00 AM|18
11/6/2005 1:00:00 AM|21
11/6/2005 1:02:00 AM|18
11/6/2005 1:03:00 AM|21
11/6/2005 1:09:00 AM|103D
11/6/2005 1:09:00 AM|67A
11/6/2005 1:13:00 AM|107
11/6/2005 1:15:00 AM|18
11/6/2005 1:16:00 AM|21
11/6/2005 1:18:00 AM|62A
11/6/2005 1:20:00 AM|18
11/6/2005 1:21:00 AM|107C

Just for starters, I'd like to get a count of calls received by shift, e.g.:

Shift, Count
First Shift, 238
Second Shift, 580
Third Shift, 329

Later, I'd like to get to this:

Shift, Type, Count
First Shift, 18, 62
First Shift, 107C, 89
First Shift, 62A, 9

...

Second Shift, 18, 48
Second Shift, 107C, 67
Second Shift, 62A, 27

...

Third Shift, 18, 91
Third Shift, 107C, 54
Third Shift, 62A, 16

I have a start, but now I have a mental block and don't know how to move forward. So far, this is what I've done. Here, I'm just using hard-coded dates to simplify the process. Later, of course, I'll want to pass the actual dates into a variable:

select type,
case
when date_time_rec between to_date('11-06-2005 07:00','MM-DD-YYYY HH24:MI')
  and to_date('11-06-2005 14:59','MM-DD-YYYY HH24:MI') then
'FirstShift'

  when date_time_rec between to_date('11-06-2005 15:00','MM-DD-YYYY HH24:MI')
  and to_date('11-06-2005 22:59','MM-DD-YYYY HH24:MI') then
'SecondShift'

  when date_time_rec between to_date('11-06-2005 23:00','MM-DD-YYYY HH24:MI')
  and to_date('11-07-2005 06:59','MM-DD-YYYY HH24:MI') then
'ThirdShift'

end as Shift
from complain
where date_time_rec
between '06-NOV-05' and '07-NOV-05'

I sure would appreciate a hand with this. Frankly, I'm really surprised I couldn't find any posts on this. It seems like it would be a common type of query.

My organization used Oracle 8i. Received on Wed Nov 09 2005 - 12:34:52 CST