Compare calls between times - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Compare calls between times

Author

Topic

BigJohnson
Starting Member

9 Posts

Posted - 2006-08-01 : 11:51:13

Hi all
I've been asked to create a query/report that displays the number of calls received between certain timeframes - but I've not worked with times only dates. Can anyone point me in the right direction?

eg
0800-0859 (5 calls), 0900-0959(25 calls), 1000-1059(15 calls) etc

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-01 : 12:01:34

Define your time frame

Madhivanan

Failing to plan is Planning to fail

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-01 : 12:33:25

U may need to learn about Date-Time related stuff and how to query a table with the Keyword between.

eg. Select Count(*) from CallTable where CallTime between .... and ....

Edit :
The above seems to be not correct

if convert(int,convert(varchar (2),getdate(),108)) > 11 and convert(int,convert(varchar (2),getdate(),108)) <= 12
	print 'Time Frame is 11.00 AM to 12.00 Noon'
if convert(int,convert(varchar (2),getdate(),108)) > 12 and convert(int,convert(varchar (2),getdate(),108)) <= 13
	print 'Time Frame is 12.00 Noon to 1.00 PM'
if convert(int,convert(varchar (2),getdate(),108)) > 13 and convert(int,convert(varchar (2),getdate(),108)) <= 14
	print 'Time Frame is 1.00 PM to 2.00 PM'

Srinika

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-01 : 13:08:11

[code]-- prepare test data
declare @calls table (dt datetime, number int)

insert @calls
select '20060701 14:12:15', 2121323 union all
select '20060701 15:11:15', 2121323 union all
select '20060701 15:17:15', 2121323 union all
select '20060701 09:12:15', 2121323 union all
select '20060701 11:45:15', 2121323 union all
select '20060701 13:12:15', 2121323 union all
select '20060701 14:55:15', 2121323 union all
select '20060701 11:12:15', 2121323 union all
select '20060701 10:05:15', 2121323 union all
select '20060701 09:12:15', 2121323

-- do the work
SELECT CONVERT(VARCHAR(5), DATEADD(hour, z.HourSlot, 0), 108) + '-' + CONVERT(VARCHAR(5), DATEADD(minute, 59, DATEADD(hour, z.HourSlot, 0)), 108) TimeSlot,
COUNT(c.dt) Calls
FROM (
SELECT b2.h + b1.h + b0.h HourSlot
FROM (SELECT 0 h UNION ALL SELECT 1) b0
CROSS JOIN (SELECT 0 h UNION ALL SELECT 2 UNION ALL SELECT 4) b1
CROSS JOIN (SELECT 0 h UNION ALL SELECT 6 UNION ALL SELECT 12 UNION ALL SELECT 18) b2
) z
LEFT JOIN @calls c ON DATEPART(hour, c.dt) = z.HourSlot
AND DATEADD(day, DATEDIFF(day, 0, c.dt), 0) = '20060701'
WHERE z.HourSlot BETWEEN 8 and 17
GROUP BY z.HourSlot
ORDER BY z.HourSlot[/code]
Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-01 : 21:22:15

Call Count by hour:

select
	Hour = dateadd(hh,datediff(hh,0,MyTime),0),
	CALL_COUNT = count(*)
from
	MyTable
Where
	-- Select date of 2006-08-01
	MyDate >= '20060801' and
	MyDate <  '20060802'
group by
	-- Group by start of hour that call is in
	dateadd(hh,datediff(hh,0,MyTime),0)
order by
	-- order by start of hour that call is in
	dateadd(hh,datediff(hh,0,MyTime),0)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources