Re: SQL count by time intervals

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Wed, 9 Nov 2005 20:43:00 +0100
Message-ID: <43725143$0$28022$626a14ce@news.free.fr>

<schroeder915_at_yahoo.com> a écrit dans le message de news: 1131561292.154818.172720_at_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

<snip>
|
| 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
|

<snip>
|
|
| My organization used Oracle 8i.
|

Try this one:

select trunc(date_time_rec-7/24) "Day",

       trunc(to_number(to_char(date_time_rec-7/24,'HH24'))/8) "Shift",
       "TYPE" "Type",
       count(*) "Nb Calls"

from MyTable
group by trunc(date_time_rec-7/24),

         trunc(to_number(to_char(date_time_rec-7/24,'HH24'))/8),
         "TYPE"

order by 1, 2, 3
/

Regards
Michel Cadot Received on Wed Nov 09 2005 - 13:43:00 CST