SQL Query for MI Report

  • 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!


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • 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.

  • 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!


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • 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

  • 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.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • 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.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply