June 14, 2011 at 9:48 am
Hey,
I've been having some difficulties writing a query for an MI report. Basically, the report is supposed to show the number of calls that are active at a specific time interval between the opening hours of the call centre on a specific day.
E.g.
If a call started at 8:15am and lasted for 120 seconds and the query was looking at 1 minute intervals, then the call would show up on the 8:15 and 8:16 time intervals (call starts are recorded to the nearest minute, the MI report is supposed to be a rough estimate).
I've successfully written a query that does this for when a call starts, but not for the call duration. So for the give example my query would only allow that call to show up on the 8:15 interval.
I could do with some help please 🙂
--WHOOPS! Turns out my sample data was crashing peoples browsers! So I've attached the original sample data (30k rows) to the post. Sorry!--
USE [master]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temptable]') AND type in (N'U'))
DROP TABLE [dbo].[temptable]
GO
CREATE TABLE [dbo].[temptable](
[historyID] [bigint] NULL,
[contactdate] [datetime] NULL,
[connecttime] [decimal](10, 3) NULL
) ON [PRIMARY]
INSERT INTO [dbo].[temptable] (historyID, contactdate, connecttime)
SELECT '8351830','Jun 13 2011 5:23AM','9.703' UNION ALL
SELECT '8351831','Jun 13 2011 7:59AM','0.000' UNION ALL
SELECT '8351832','Jun 13 2011 7:59AM','0.000' UNION ALL
SELECT '8351833','Jun 13 2011 7:59AM','15.375' UNION ALL
SELECT '8351834','Jun 13 2011 8:00AM','0.000' UNION ALL
SELECT '8351835','Jun 13 2011 8:00AM','0.000' UNION ALL
SELECT '8351836','Jun 13 2011 8:00AM','0.000' UNION ALL
SELECT '8351837','Jun 13 2011 8:00AM','0.000' UNION ALL
SELECT '8351838','Jun 13 2011 8:00AM','0.000' UNION ALL
SELECT '8351839','Jun 13 2011 8:00AM','2.359' UNION ALL
SELECT '8351840','Jun 13 2011 8:00AM','0.000' UNION ALL
SELECT '8351841','Jun 13 2011 8:00AM','0.000' UNION ALL
SELECT '8351842','Jun 13 2011 7:59AM','34.265' UNION ALL
SELECT '8351843','Jun 13 2011 8:00AM','0.000' UNION ALL
SELECT '8351844','Jun 13 2011 8:00AM','0.000' UNION ALL
SELECT '8351845','Jun 13 2011 8:00AM','0.000' UNION ALL
SELECT '8351846','Jun 13 2011 8:01AM','0.000' UNION ALL
SELECT '8351847','Jun 13 2011 8:01AM','7.234' UNION ALL
SELECT '8351848','Jun 13 2011 8:01AM','19.250' UNION ALL
SELECT '8351849','Jun 13 2011 8:01AM','12.906' UNION ALL
SELECT '8351850','Jun 13 2011 8:01AM','2.750' UNION ALL
SELECT '8351851','Jun 13 2011 8:00AM','68.642' UNION ALL
SELECT '8351852','Jun 13 2011 8:01AM','0.000' UNION ALL
SELECT '8351853','Jun 13 2011 8:02AM','0.000' UNION ALL
SELECT '8351854','Jun 13 2011 8:01AM','19.625' UNION ALL
SELECT '8351855','Jun 13 2011 8:02AM','0.000' UNION ALL
SELECT '8351856','Jun 13 2011 8:02AM','0.000' UNION ALL
SELECT '8351857','Jun 13 2011 8:02AM','0.000' UNION ALL
SELECT '8351858','Jun 13 2011 8:02AM','0.000' UNION ALL
SELECT '8351859','Jun 13 2011 8:02AM','16.593' UNION ALL
SELECT '8351860','Jun 13 2011 8:02AM','0.000' UNION ALL
SELECT '8351861','Jun 13 2011 8:02AM','0.000' UNION ALL
SELECT '8351862','Jun 13 2011 8:03AM','0.000' UNION ALL
SELECT '8351863','Jun 13 2011 8:02AM','18.937' UNION ALL
SELECT '8351864','Jun 13 2011 8:03AM','6.875' UNION ALL
SELECT '8351865','Jun 13 2011 8:00AM','12.718' UNION ALL
SELECT '8351866','Jun 13 2011 8:03AM','0.000' UNION ALL
SELECT '8351867','Jun 13 2011 8:03AM','27.046' UNION ALL
SELECT '8351868','Jun 13 2011 8:03AM','0.000' UNION ALL
SELECT '8351869','Jun 13 2011 8:03AM','5.369' UNION ALL
SELECT '8351870','Jun 13 2011 8:03AM','17.218' UNION ALL
SELECT '8351871','Jun 13 2011 8:03AM','28.656' UNION ALL
SELECT '8351872','Jun 13 2011 8:03AM','10.343' UNION ALL
SELECT '8351873','Jun 13 2011 8:04AM','0.000' UNION ALL
SELECT '8351874','Jun 13 2011 8:05AM','0.000' UNION ALL
SELECT '8351875','Jun 13 2011 8:04AM','12.812' UNION ALL
SELECT '8351876','Jun 13 2011 8:05AM','12.906' UNION ALL
SELECT '8351877','Jun 13 2011 8:06AM','34.876' UNION ALL
SELECT '8351878','Jun 13 2011 8:07AM','0.000' UNION ALL
SELECT '8351879','Jun 13 2011 8:05AM','104.611' UNION ALL
SELECT '8351880','Jun 13 2011 8:03AM','153.560' UNION ALL
SELECT '8351881','Jun 13 2011 8:08AM','0.000' UNION ALL
SELECT '8351882','Jun 13 2011 8:08AM','0.000' UNION ALL
SELECT '8351883','Jun 13 2011 8:08AM','2.640' UNION ALL
SELECT '8351884','Jun 13 2011 8:08AM','0.000' UNION ALL
SELECT '8351885','Jun 13 2011 8:07AM','40.353' UNION ALL
SELECT '8351886','Jun 13 2011 8:04AM','216.173' UNION ALL
SELECT '8351887','Jun 13 2011 8:08AM','16.687' UNION ALL
SELECT '8351888','Jun 13 2011 8:08AM','0.000' UNION ALL
SELECT '8351889','Jun 13 2011 8:08AM','0.000' UNION ALL
SELECT '8351890','Jun 13 2011 8:05AM','215.974' UNION ALL
SELECT '8351891','Jun 13 2011 8:08AM','43.617' UNION ALL
SELECT '8351892','Jun 13 2011 8:09AM','2.779' UNION ALL
SELECT '8351893','Jun 13 2011 8:09AM','15.041' UNION ALL
SELECT '8351894','Jun 13 2011 8:08AM','48.494' UNION ALL
SELECT '8351895','Jun 13 2011 8:09AM','0.000' UNION ALL
SELECT '8351896','Jun 13 2011 8:09AM','19.570' UNION ALL
SELECT '8351897','Jun 13 2011 8:09AM','0.000' UNION ALL
SELECT '8351898','Jun 13 2011 8:10AM','0.000' UNION ALL
SELECT '8351899','Jun 13 2011 8:04AM','356.923' UNION ALL
SELECT '8351900','Jun 13 2011 8:10AM','0.000' UNION ALL
SELECT '8351901','Jun 13 2011 8:10AM','0.000' UNION ALL
SELECT '8351902','Jun 13 2011 8:10AM','3.546' UNION ALL
SELECT '8351903','Jun 13 2011 8:10AM','0.000' UNION ALL
SELECT '8351904','Jun 13 2011 8:10AM','0.000' UNION ALL
SELECT '8351905','Jun 13 2011 8:10AM','0.000' UNION ALL
SELECT '8351906','Jun 13 2011 8:10AM','0.000' UNION ALL
SELECT '8351907','Jun 13 2011 8:10AM','0.000' UNION ALL
SELECT '8351908','Jun 13 2011 8:10AM','0.000' UNION ALL
SELECT '8351909','Jun 13 2011 8:10AM','22.414' UNION ALL
SELECT '8351910','Jun 13 2011 8:08AM','129.830' UNION ALL
SELECT '8351911','Jun 13 2011 8:11AM','3.484' UNION ALL
SELECT '8351912','Jun 13 2011 8:05AM','243.828' UNION ALL
SELECT '8351913','Jun 13 2011 8:09AM','66.925' UNION ALL
SELECT '8351914','Jun 13 2011 8:08AM','113.812' UNION ALL
SELECT '8351915','Jun 13 2011 8:11AM','0.000' UNION ALL
SELECT '8351916','Jun 13 2011 8:12AM','0.000' UNION ALL
SELECT '8351917','Jun 13 2011 8:11AM','23.578'
Here's the code that I've written so far, to give you an idea of where I got to.
USE [master]
GO
DECLARE @starttime DATETIME, @endtime DATETIME, @interval INT
SET @starttime = '2011-06-13 07:45:00'
SET @endtime = '2011-06-13 20:30:00'
--Minutes
SET @interval = 1
--Actually, I'm using a tally table. But for testing purposes, here is one on the fly
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
t5 AS (SELECT 1 N FROM t4 x, t4 y),
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t4 x, t4 y)
--Actual query
SELECT DATEADD(MINUTE,-@interval,DATEADD(MINUTE,N*@interval,@starttime)) AS actualTime, ISNULL(numberOfActiveCalls,0) AS numberOfActiveCalls
FROM tally
LEFT OUTER JOIN (SELECT COUNT(*) AS numberOfActiveCalls, times.startinterval
FROM master.dbo.temptable
CROSS APPLY (SELECT DATEADD(MINUTE,-@interval,DATEADD(MINUTE,N*@interval,@starttime)) AS startinterval,
DATEADD(MINUTE,N*@interval,@starttime) AS endinterval
FROM tally
WHERE DATEADD(MINUTE,-@interval,DATEADD(MINUTE,N*@interval,@starttime)) <= @endtime) AS times
WHERE contactdate >= times.startinterval AND contactdate < times.endinterval
GROUP BY times.startinterval) a ON DATEADD(MINUTE,-@interval,DATEADD(MINUTE,N*@interval,@starttime)) = a.startinterval
WHERE DATEADD(MINUTE,-@interval,DATEADD(MINUTE,N*@interval,@starttime)) <= @endtime
ORDER BY DATEADD(MINUTE,-@interval,DATEADD(MINUTE,N*@interval,@starttime)) ASC
Any help would be greatly appreciated!
June 14, 2011 at 11:38 am
I might suggest reposting this thread with the scripts as an attachment. The Sample data scipt you posed is too large and causes the browser to lock up. I took me forever to get this posted.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 14, 2011 at 12:23 pm
Dan.Humphries (6/14/2011)
I might suggest reposting this thread with the scripts as an attachment. The Sample data scipt you posed is too large and causes the browser to lock up. I took me forever to get this posted.
Whoops, too many rows of sample data I guess. Fixed now, thanks for letting me know!
June 14, 2011 at 1:39 pm
A couple of things that I wanted to point out.
* You use this formula repeatedly in your query DATEADD(MINUTE,-@interval,DATEADD(MINUTE,N*@interval,@starttime)) I moved it into the CTE so that I could name it there and just use the name in the rest of the query.
* This formula can also be simplified to only call DATEADD() once instead of twice.
DATEADD(MINUTE,(N-1)*@interval,@starttime))
* In this case, the records returned is determined by the date range and since the Tally table sets the date range, you can limit the records there and use the joins to make sure that only the appropriate history records are pulled.
* You are referencing the Tally table twice: once on the left side of the LEFT OUTER JOIN and once on the right side of the CROSS APPLY. You can collapse both of these by using an OUTER APPLY.
Note: I changed your table to a table variable.
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
t5 AS (SELECT 1 N FROM t4 x, t4 y),
Tally AS (
SELECT TOP ( SELECT Datediff(MINUTE, @starttime, @endtime ) + 1 ) DATEADD(MINUTE,(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1)*@interval,@starttime) AS ActualTime
FROM t4 x, t4 y
)
--Actual query
SELECT ActualTime
, ISNULL(numberOfActiveCalls,0) AS numberOfActiveCalls
FROM tally
OUTER APPLY (
SELECT Count(*) AS numberOfActiveCalls
FROM @temptable
WHERE ActualTime >= ContactDate
AND ContactDate >= DateAdd(MINUTE, -ConnectTime, ActualTime)
) AS Contacts
I assumed that might have/need an index on ContactDate, so I wrote the WHERE clause to take advantage of such an index.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 14, 2011 at 2:52 pm
drew.allen (6/14/2011)
A couple of things that I wanted to point out.* You use this formula repeatedly in your query
DATEADD(MINUTE,-@interval,DATEADD(MINUTE,N*@interval,@starttime))I moved it into the CTE so that I could name it there and just use the name in the rest of the query.* This formula can also be simplified to only call DATEADD() once instead of twice.
DATEADD(MINUTE,(N-1)*@interval,@starttime))* In this case, the records returned is determined by the date range and since the Tally table sets the date range, you can limit the records there and use the joins to make sure that only the appropriate history records are pulled.
* You are referencing the Tally table twice: once on the left side of the LEFT OUTER JOIN and once on the right side of the CROSS APPLY. You can collapse both of these by using an OUTER APPLY.
I assumed that might have/need an index on ContactDate, so I wrote the WHERE clause to take advantage of such an index.
Drew
That's fantastic Drew, thanks. I'll have to look into OUTER APPLY, since it's not something I've used before.
June 17, 2011 at 4:19 am
Just to keep anyone that is interested informed, the final version : -
DECLARE @starttime DATETIME, @endtime DATETIME, @interval INT
SET @starttime = '2011-06-13 07:45:00'
SET @endtime = '2011-06-13 20:30:00'
--Minutes
SET @interval = 1
;WITH t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x, t1 y),
t3 AS (SELECT 1 N FROM t2 x, t2 y),
t4 AS (SELECT 1 N FROM t3 x, t3 y),
t5 AS (SELECT 1 N FROM t4 x, t4 y),
Tally AS (SELECT TOP (SELECT DATEDIFF(MINUTE, @starttime, @endtime ) + 1 )
DATEADD(MINUTE,(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1)*@interval,@starttime)
AS ActualTime
FROM t4 x, t4 y)
SELECT ActualTime, ISNULL(calls.numberOfActiveCalls,0) AS numberOfActiveCalls
FROM tally
OUTER APPLY (SELECT COUNT(*) AS numberOfActiveCalls
FROM (SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, 0, contactdate)/@interval * @interval, 0) AS zerocontactdate,
contactdate, connecttime
FROM master.dbo.temptable
WHERE connecttime > 0 AND contactdate >= @starttime AND contactdate <= @endtime) a
WHERE zerocontactdate <= ActualTime AND (contactdate >= DATEADD(SECOND, -ConnectTime, ActualTime))
) AS calls
WHERE ActualTime <= @endtime
It's slower than I wanted, but I can't seem to do much about it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply