Re: SQL count by time intervals

First, Michel, thank you for your kind assistance.

I love to see things done in ways that I haven't thought of. In truth, I'm still trying to ascertain what it's doing.

If, for example, I parse out the pieces of the statement that manipulate time, the results are not what I'd expect (as below).

The query you wrote does very elegantly split the events into three discrete categories. The method appears to be to subract 7 hours, truncate to the hour portion of the result, and divide by 8 (there being three increments of 8 in a 24 hour period. I can't say I understand why you're substracting 7 hours.

In any event, if I wish to specify particular time ranges, I don't believe I can get there with this.

 7:00AM - 2:59PM
3PM - 10:59PM
11PM - 6:59AM

So, it seems like that still takes me back to using a case statement?

select date_time_rec,
date_time_rec-7/24,
to_char(date_time_rec-7/24,'HH24'),
trunc(to_number(to_char(date_time_rec-7/24,'HH24'))/8) "Shift" from complain
where date_time_rec
between '06-NOV-05' and '07-NOV-05'

DATE_TIME_REC,DATE_TIME_REC-7/24,TO_CHAR(DATE_TIME_REC-7/24,'HH24'),Shift

11/6/2005 12:06:00 AM,11/5/2005 5:06:00 PM,17,2
11/6/2005 12:06:00 AM,11/5/2005 5:06:00 PM,17,2
11/6/2005 12:09:00 AM,11/5/2005 5:09:00 PM,17,2

11/6/2005 7:01:00 AM,11/6/2005 12:01:00 AM,00,0
11/6/2005 7:02:00 AM,11/6/2005 12:02:00 AM,00,0
11/6/2005 7:03:00 AM,11/6/2005 12:03:00 AM,00,0

11/6/2005 3:00:00 PM,11/6/2005 8:00:00 AM,08,1
11/6/2005 3:00:00 PM,11/6/2005 8:00:00 AM,08,1
11/6/2005 3:02:00 PM,11/6/2005 8:02:00 AM,08,1