Re: SQL count by time intervals

See if this works (i assumed the call type column to be named call_type)

bre on shift skip 2 on complaint_type skip 1 compute sum of cc on shift

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

regards
srivenu Received on Wed Nov 09 2005 - 13:57:56 CST