Help needed w/query
-
One of our developers asked for my assistance with a query he's currently working on.
We have a call record table that contains an individual record for each telephone call placed within our system. Each record has a datetime column for the call start time (StartTime), end time (EndTime), and an int column for the length of the call in seconds (duration).
He is trying to find out the maximum number of telephone calls that go through our system at any particular time. Immediately, I think all I need to do is just count the number of records for a specific time (ie. 2007-04-17 14:25:00.000) or a specific range (ie. 2007-04-17 14:25:00.000 to 2007-04-17 16:00:00.000). However, he wants to know what was the maximum number of calls that occurred on the system, and the time range when it occurred. So basically, he would like the result set to give HIM the time range, instead of the other way around.
I'm trying to figure out the best way to do this given the datetime ranges for each record. I thought this may be possible with a single elaborate query, by somehow grouping the ranges and using aggregate functions, but now I'm thinking I should take some other approach.
I'm wondering if I need to write a cursor to sort the records based on the start/end times, and step-through each record to build a separate table that maintains the times and a running count whenever a call ends and a new one begins. Or maybe I need to take some entirely different approach.
Any feedback on this would be greatly appreciated. Thanks.
-
What level of granularity are you looking at? Hour, 30 minutes, 10 minutes, minute, second, millisecond, etc? While initially I didn't see a way it could be done without a loop of some sort, I'm thinking that breaking it down by granularity, and using a numbers table to represent each level of granularity, might do the trick.
-
You will need
1. a table of time intervals that you cross join to your data table
2. an function which, for 2 datetime interval pairs, calculates the amount of overlap
-
maybe this can help you out ....
create table #PhoneCalls (CallId int not null identity(1,1) primary key, CallTime datetime not null, CallEnd Datetime not null default ('9999-12-31 00:00:00.000'), CallDuration int not null default -1 )
set nocount on
insert into #PhoneCalls (CallTime, CallEnd ) values (getdate(),dateadd(ss,25,getdate()))
insert into #PhoneCalls (CallTime, CallEnd ) values (getdate(),dateadd(ss,125,getdate()))
insert into #PhoneCalls (CallTime, CallEnd ) values (getdate(),dateadd(ss,35,getdate()))
insert into #PhoneCalls (CallTime ) values (getdate())
set nocount off
SELECT CASE WHEN (GROUPING(CallDate) = 1) THEN 'ALL'
ELSE cast(CallDate as char(10))
END AS CallDate,
CASE WHEN (GROUPING(CallHour) = 1) THEN 'ALL'
ELSE cast(CallHour as varchar(15))
END AS CallHour,
CASE WHEN (GROUPING(CallMinute) = 1) THEN 'ALL'
ELSE cast(CallMinute as char(3))
END AS CallMinute,
count(*) AS QtyCalls
FROM (
select CallTime , CallEnd
, datepart(dd,CallTime) as CallDate
, datepart(hh,CallTime) as CallHour
, datepart(mi,CallTime) as CallMinute
from #PhoneCalls
-- where @yourdate between CallTime and CallEnd
) A
GROUP BY CallDate, CallHour, CallMinute WITH ROLLUP
drop table #PhoneCalls
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I would be interested in knowing how well something like the following works:
-- *** Test Data ***
CREATE TABLE #YourTable
(
StartTime datetime NOT NULL
,EndTime datetime NULL
)
GO
INSERT INTO #YourTable
SELECT '20070419 08:32', NULL UNION ALL
SELECT '20070419 08:33', '20070419 08:52' UNION ALL
SELECT '20070419 08:35', '20070419 09:00' UNION ALL
SELECT '20070419 08:38', '20070419 08:39' UNION ALL
SELECT '20070419 08:53', '20070419 09:01'
-- *** End Test Data ***
GO
CREATE TABLE #Temp
(
cTime datetime NOT NULL PRIMARY KEY CLUSTERED
,Increment int NOT NULL
,Calls int NULL
)
GO
INSERT INTO #Temp (cTime, Increment)
SELECT cTime
,SUM(Increment) AS Increment
FROM (
SELECT StartTime, 1
FROM #YourTable
UNION ALL
SELECT EndTime, -1
FROM #YourTable
WHERE EndTime IS NOT NULL
) D (cTime, Increment)
GROUP BY cTime
HAVING SUM(Increment) <> 0
OPTION (MAXDOP 1)
DECLARE @Total int
SET @Total = 0
UPDATE #Temp
SET @Total = Calls = @Total + Increment
OPTION (MAXDOP 1)
SELECT T1.cTime AS FromTime
,T2.cTime AS ToTime
,T1.Calls AS MaxCalls
FROM #Temp T1
CROSS JOIN #Temp T2
WHERE T1.Calls = (
SELECT MAX(T4.Calls)
FROM #Temp T4
)
AND T2.cTime = (
SELECT MIN(T3.cTime)
FROM #TEMP T3
WHERE T3.cTime > T1.cTime
AND T3.Calls < T1.Calls
)
-
try this query
select count(*), cast(floor(cast(startTime as float)*24)/24 as datetime)
from calltable
where datediff(hh, startTime, getdate()) < 24
group by cast(floor(cast(startTime as float)*24)/24 as datetime)
order by 1 desc
-
Thanks to everyone on their responses!
Ken, I think I may be able to use what you posted. I created your test table, ran the query, and came up with the below result set:
FromTime ToTime MaxCalls
------------------------------------------------------ ------------------------------------------------------ -----------
2007-04-19 08:38:00.000 2007-04-19 08:39:00.000 4
(1 row(s) affected)
This is exactly the kind of information I'm looking for. Based on the times in your test table, the output appears to be correct. The max number of calls at any one time was 4, and it occurred between 8:38:00.000 and 8:39:00.000.
I'm going to do some additional testing (enter some additional records at other times, etc) and see what happens. If all looks good, then I need to look at your code and figure out how/why it works!
I'll let you know how it turns out.
Thanks again to everyone for your help!
-
Sorry, forgot to respond sooner...
Ken, I thoroughly tested your solution and it works great!
I implemented this into a stored procedure, and made a slight modification where the user can pass an optional date range to find the max number of calls over a specified period (a few days, months, years, etc).
I went over your code and can see the reason for splitting out the start and end times, each representing a separate row with an incremental field in the work table. Logically, this makes a whole lot of sense, and also makes it a lot easier to work with. Unfortunately, I probably wouldn't have come up w/that on my own.
I really appreciate your help on this! You da man!
Also, thanks to everyone else for their feedback!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply