Re: SQL count by time intervals

From: <srivenu_at_hotmail.com>
Date: 9 Nov 2005 20:42:11 -0800
Message-ID: <1131597731.194072.191600@f14g2000cwb.googlegroups.com>

select	shift, call_type, count(*) cc
from 	(
	select
		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,
	        call_type
	from 	complain
	where	date_time_rec between '06-NOV-05' and '07-NOV-05'
	)
	iv

group by shift,call_type
/

But as Hans said Michel's approach is elegant

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

group by
trunc(to_number(to_char(date_time_rec-7/24,'HH24'))/8),call_type
/

regards
srivenu Received on Wed Nov 09 2005 - 22:42:11 CST