Time Intervals

  • This is driving me slowly insane. . . :hehe:

    I've been tasked to create an SSRS report showing which agents are logged in during 15 minute intervals for a particular day.

    However, although currently the requirement is 15 minute intervals it is suggested that this could change fairly frequently depending on who wants to view the report.

    I can do nothing with the table layouts, since these are setup by the company that builds are agents software, but here is some dummy data (showing just the parts of the tables that I need)

    DROP TABLE #Agents

    GO

    CREATE TABLE #Agents (

    AgentID INT IDENTITY,

    Agent varchar(255) default NULL)

    INSERT INTO #Agents (Agent) VALUES ('Megan');

    INSERT INTO #Agents (Agent) VALUES ('Tana');

    INSERT INTO #Agents (Agent) VALUES ('Candice');

    INSERT INTO #Agents (Agent) VALUES ('Kaden');

    INSERT INTO #Agents (Agent) VALUES ('Aspen');

    INSERT INTO #Agents (Agent) VALUES ('Vladimir');

    INSERT INTO #Agents (Agent) VALUES ('Olga');

    INSERT INTO #Agents (Agent) VALUES ('Alexa');

    INSERT INTO #Agents (Agent) VALUES ('Evan');

    INSERT INTO #Agents (Agent) VALUES ('Megan');

    INSERT INTO #Agents (Agent) VALUES ('Otto');

    INSERT INTO #Agents (Agent) VALUES ('Philip');

    INSERT INTO #Agents (Agent) VALUES ('Ian');

    INSERT INTO #Agents (Agent) VALUES ('Wynter');

    INSERT INTO #Agents (Agent) VALUES ('Mechelle');

    INSERT INTO #Agents (Agent) VALUES ('Nicholas');

    INSERT INTO #Agents (Agent) VALUES ('Lana');

    INSERT INTO #Agents (Agent) VALUES ('Duncan');

    INSERT INTO #Agents (Agent) VALUES ('Solomon');

    INSERT INTO #Agents (Agent) VALUES ('Mercedes');

    INSERT INTO #Agents (Agent) VALUES ('Sierra');

    INSERT INTO #Agents (Agent) VALUES ('Odette');

    INSERT INTO #Agents (Agent) VALUES ('Reece');

    INSERT INTO #Agents (Agent) VALUES ('Guinevere');

    INSERT INTO #Agents (Agent) VALUES ('Michael');

    INSERT INTO #Agents (Agent) VALUES ('Harper');

    INSERT INTO #Agents (Agent) VALUES ('Barclay');

    INSERT INTO #Agents (Agent) VALUES ('Dane');

    INSERT INTO #Agents (Agent) VALUES ('Lewis');

    INSERT INTO #Agents (Agent) VALUES ('Carlos');

    INSERT INTO #Agents (Agent) VALUES ('Aubrey');

    INSERT INTO #Agents (Agent) VALUES ('Demetria');

    INSERT INTO #Agents (Agent) VALUES ('Angela');

    INSERT INTO #Agents (Agent) VALUES ('Dale');

    INSERT INTO #Agents (Agent) VALUES ('Quail');

    INSERT INTO #Agents (Agent) VALUES ('Brianna');

    INSERT INTO #Agents (Agent) VALUES ('Celeste');

    INSERT INTO #Agents (Agent) VALUES ('Joelle');

    INSERT INTO #Agents (Agent) VALUES ('Cally');

    INSERT INTO #Agents (Agent) VALUES ('Jillian');

    INSERT INTO #Agents (Agent) VALUES ('Caleb');

    INSERT INTO #Agents (Agent) VALUES ('Talon');

    INSERT INTO #Agents (Agent) VALUES ('Dane');

    INSERT INTO #Agents (Agent) VALUES ('Lacey');

    INSERT INTO #Agents (Agent) VALUES ('Shea');

    INSERT INTO #Agents (Agent) VALUES ('Tara');

    INSERT INTO #Agents (Agent) VALUES ('Dolan');

    INSERT INTO #Agents (Agent) VALUES ('Ignatius');

    INSERT INTO #Agents (Agent) VALUES ('Catherine');

    INSERT INTO #Agents (Agent) VALUES ('Bruce');

    INSERT INTO #Agents (Agent) VALUES ('Barclay');

    INSERT INTO #Agents (Agent) VALUES ('Ali');

    INSERT INTO #Agents (Agent) VALUES ('Shelly');

    INSERT INTO #Agents (Agent) VALUES ('Susan');

    INSERT INTO #Agents (Agent) VALUES ('Henry');

    INSERT INTO #Agents (Agent) VALUES ('Keaton');

    INSERT INTO #Agents (Agent) VALUES ('Desiree');

    INSERT INTO #Agents (Agent) VALUES ('Joel');

    INSERT INTO #Agents (Agent) VALUES ('Quamar');

    INSERT INTO #Agents (Agent) VALUES ('Sigourney');

    INSERT INTO #Agents (Agent) VALUES ('Martina');

    INSERT INTO #Agents (Agent) VALUES ('Sebastian');

    INSERT INTO #Agents (Agent) VALUES ('Brandon');

    INSERT INTO #Agents (Agent) VALUES ('Neve');

    INSERT INTO #Agents (Agent) VALUES ('Aspen');

    INSERT INTO #Agents (Agent) VALUES ('Summer');

    INSERT INTO #Agents (Agent) VALUES ('Salvador');

    INSERT INTO #Agents (Agent) VALUES ('Davis');

    INSERT INTO #Agents (Agent) VALUES ('Griffin');

    INSERT INTO #Agents (Agent) VALUES ('Yetta');

    INSERT INTO #Agents (Agent) VALUES ('Ori');

    INSERT INTO #Agents (Agent) VALUES ('Kareem');

    INSERT INTO #Agents (Agent) VALUES ('Sonia');

    INSERT INTO #Agents (Agent) VALUES ('Xanthus');

    INSERT INTO #Agents (Agent) VALUES ('Aurora');

    INSERT INTO #Agents (Agent) VALUES ('Norman');

    INSERT INTO #Agents (Agent) VALUES ('Skyler');

    INSERT INTO #Agents (Agent) VALUES ('Geoffrey');

    INSERT INTO #Agents (Agent) VALUES ('Burke');

    INSERT INTO #Agents (Agent) VALUES ('Allen');

    INSERT INTO #Agents (Agent) VALUES ('Jessamine');

    INSERT INTO #Agents (Agent) VALUES ('Cynthia');

    INSERT INTO #Agents (Agent) VALUES ('Aristotle');

    INSERT INTO #Agents (Agent) VALUES ('Gage');

    INSERT INTO #Agents (Agent) VALUES ('Blair');

    INSERT INTO #Agents (Agent) VALUES ('Jaquelyn');

    INSERT INTO #Agents (Agent) VALUES ('Slade');

    INSERT INTO #Agents (Agent) VALUES ('Gwendolyn');

    INSERT INTO #Agents (Agent) VALUES ('Wendy');

    INSERT INTO #Agents (Agent) VALUES ('Maggy');

    INSERT INTO #Agents (Agent) VALUES ('Dacey');

    INSERT INTO #Agents (Agent) VALUES ('Lillian');

    INSERT INTO #Agents (Agent) VALUES ('Jarrod');

    INSERT INTO #Agents (Agent) VALUES ('Gavin');

    INSERT INTO #Agents (Agent) VALUES ('Eric');

    INSERT INTO #Agents (Agent) VALUES ('Kennedy');

    INSERT INTO #Agents (Agent) VALUES ('Cora');

    INSERT INTO #Agents (Agent) VALUES ('Brock');

    INSERT INTO #Agents (Agent) VALUES ('Ray');

    INSERT INTO #Agents (Agent) VALUES ('Leo');

    DROP TABLE #AgentSession

    GO

    CREATE TABLE #AgentSession (

    AgentID varchar(50) default NULL,

    sessionstart DATETIME default NULL,

    sessionend DATETIME default NULL

    )

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('71','2010-11-22 10:51:02');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('23','2010-11-22 04:41:16');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('56','2010-11-22 02:44:10');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('54','2010-11-22 22:25:23');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('10','2010-11-22 07:26:21');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('91','2010-11-22 19:52:15');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('89','2010-11-22 21:55:58');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('67','2010-11-22 23:54:55');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('8','2010-11-22 00:38:25');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('66','2010-11-22 18:32:33');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('62','2010-11-22 10:31:22');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('97','2010-11-22 12:29:10');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('59','2010-11-22 12:11:58');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('67','2010-11-22 10:00:44');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('8','2010-11-22 10:11:38');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('50','2010-11-22 13:37:26');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('46','2010-11-22 04:59:15');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('28','2010-11-22 05:57:53');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('78','2010-11-22 12:16:58');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('34','2010-11-22 17:36:25');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('4','2010-11-22 23:54:44');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('60','2010-11-22 21:59:48');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('41','2010-11-22 00:53:28');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('100','2010-11-22 17:34:51');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('41','2010-11-22 13:34:41');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('10','2010-11-22 00:32:14');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('55','2010-11-22 08:52:16');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('97','2010-11-22 23:57:19');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('24','2010-11-22 08:07:16');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('81','2010-11-22 17:09:20');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('93','2010-11-22 22:47:11');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('94','2010-11-22 10:43:54');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('3','2010-11-22 03:40:52');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('49','2010-11-22 23:14:14');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('47','2010-11-22 15:15:12');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('13','2010-11-22 00:20:21');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('40','2010-11-22 14:05:02');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('36','2010-11-22 15:07:00');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('79','2010-11-22 20:13:11');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('47','2010-11-22 11:34:16');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('1','2010-11-22 01:54:39');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('40','2010-11-22 04:58:59');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('43','2010-11-22 11:39:30');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('60','2010-11-22 12:38:00');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('7','2010-11-22 00:38:35');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('50','2010-11-22 20:43:59');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('9','2010-11-22 00:00:33');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('52','2010-11-22 19:51:46');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('78','2010-11-22 18:23:29');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('86','2010-11-22 04:51:39');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('85','2010-11-22 04:39:10');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('82','2010-11-22 17:28:31');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('46','2010-11-22 10:30:29');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('26','2010-11-22 19:26:27');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('81','2010-11-22 10:00:59');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('87','2010-11-22 11:58:48');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('35','2010-11-22 22:45:51');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('35','2010-11-22 07:04:20');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('83','2010-11-22 01:12:06');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('58','2010-11-22 19:15:00');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('15','2010-11-22 06:39:28');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('76','2010-11-22 13:20:00');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('52','2010-11-22 02:34:42');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('18','2010-11-22 15:19:50');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('25','2010-11-22 00:49:46');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('99','2010-11-22 16:15:54');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('30','2010-11-22 13:04:54');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('64','2010-11-22 07:47:20');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('34','2010-11-22 08:37:49');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('8','2010-11-22 10:29:20');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('10','2010-11-22 18:46:04');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('35','2010-11-22 02:54:55');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('48','2010-11-22 03:36:42');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('53','2010-11-22 02:38:18');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('94','2010-11-22 08:01:01');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('54','2010-11-22 15:27:57');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('3','2010-11-22 01:40:06');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('2','2010-11-22 11:46:49');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('6','2010-11-22 03:34:58');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('80','2010-11-22 05:44:58');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('88','2010-11-22 05:09:21');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('91','2010-11-22 00:34:32');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('62','2010-11-22 00:08:44');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('34','2010-11-22 02:21:54');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('16','2010-11-22 05:18:10');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('42','2010-11-22 13:09:55');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('20','2010-11-22 07:14:05');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('51','2010-11-22 15:29:38');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('77','2010-11-22 11:39:40');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('3','2010-11-22 12:01:40');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('9','2010-11-22 06:53:59');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('92','2010-11-22 06:37:19');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('78','2010-11-22 14:40:09');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('60','2010-11-22 18:52:45');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('9','2010-11-22 07:41:45');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('2','2010-11-22 23:47:15');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('58','2010-11-22 10:18:20');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('39','2010-11-22 18:37:36');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('65','2010-11-22 09:28:03');

    INSERT INTO #AgentSession (AgentID,sessionstart) VALUES ('92','2010-11-22 14:04:16');

    UPDATE #AgentSession

    SET sessionend = DATEADD(MI,ROUND(((60)*RAND()),0),sessionstart)

    In the first few minutes, I came up with a way to achieve what is required, using the following: -

    DECLARE @splitter TIME, @day DATETIME, @interval INT

    SET @splitter = '00:00'

    SET @interval = 15

    SET @day = '2010-11-22'

    DECLARE @splitTimes TABLE (

    splitTime TIME )

    INSERT INTO @splitTimes(splitTime)

    SELECT @splitter

    SET @splitter = Dateadd(MINUTE, @interval, @splitter)

    WHILE @splitter <= '20:00' BEGIN

    INSERT INTO @splitTimes (splitTime)

    SELECT @splitter

    SET @splitter = Dateadd(MINUTE, @interval, @splitter)

    END /* End While Loop */

    SELECT agent.Agent, sp.splitTime

    FROM #AgentSession sess WITH(nolock)

    LEFT OUTER JOIN #Agents agent WITH(nolock) ON sess.agentid = agent.agentid

    CROSS JOIN @splitTimes sp

    WHERE sp.splitTime BETWEEN CAST(sess.sessionstart AS TIME) AND CAST(sess.sessionend AS TIME)

    AND Dateadd(DAY, Datediff(DAY, '20101122', sess.sessionstart), '20101122') =

    Dateadd(DAY, Datediff(DAY, '20101122', @day), '20101122')

    ORDER BY sp.splitTime

    --Edit--

    Basically, I guess I'm after ideas for different ways to do this. My current solution works, but I don't like it and after a couple of hours am out of ideas 😉


    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/

  • The only thing I'm seeing is the way that you populate your @splitTimes table variable. I'd change it to a set-based method:

    -- See Jeff Moden's article

    -- The "Numbers" or "Tally" Table: What it is and how it replaces a loop.

    -- at http://www.sqlservercentral.com/articles/T-SQL/62867/.

    -- NOTE! A permanent tally table will always be MUCH faster

    -- than this inline one. See the above article to create your own!

    INSERT INTO @splitTimes

    SELECT [Time] = DATEADD(minute, N-@interval, '00:00')

    FROM Tally

    WHERE N <= 1200 + @interval -- # of minutes to span 00:00 to 20:00

    AND N % @interval = 0; -- allows any interval desired

    Edit: Actually, I do see another thing. Your where clause. By using a function on the dates being searched, you are eliminating the use of any indexes that might be available. Rewrite them so that they are in the format:

    WHERE sess.sessionstart >= <your starting date>

    AND sess.sessionend < <your ending date + 1 interval)

    In the case of what you're using:

    WHERE sess.sessionstart >= @day -- 20101122 00:00

    AND sess.sessionend < DateAdd(hour, 20, DateAdd(minute, 1, @day)) -- 20101122 20:01

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • I'd use a TallyTable based method:

    DECLARE @day DATETIME, @interval INT

    SET @interval = 15

    SET @day = '2010-11-22'

    ;

    WITH splitTimes AS

    (

    SELECT DATEADD(MINUTE, @interval * (N-1), @day) splitTime

    FROM Tally

    WHERE N<=(24*60/@interval)

    )

    SELECT

    agent.Agent,

    cast(sp.splitTime as time) as splitTime

    FROM #AgentSession sess

    LEFT OUTER JOIN #Agents agent ON sess.agentid = agent.agentid

    CROSS JOIN splitTimes sp

    WHERE sess.sessionstart >= @day -- 20101122 00:00

    AND sess.sessionend < DateAdd(hour, 20, DateAdd(minute, 1, @day)) -- 20101122 20:01

    ORDER BY sp.splitTime



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Bearing in mind the requirement for the Interval to vary I would avoid a table of time periods unless you have one for each valid time period.

    Assuming time periods start at midnight and even one second of duration within a period makes it count...here is another way to ponder...

    It looks like a lot of code with all the comments and the CROSS APPLYs but it's not really that much - honest 😉 It processes your sample data in under 50 ms.

    ;WITH tally(N) AS

    ( -- if you have a numbers table, use it...! this is here to help with testing as I don't know your numbers table

    SELECT ROW_NUMBER() OVER(ORDER BY @@SPID)

    FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(N)

    ,(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) b(N)

    ,(VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) c(N)

    )

    SELECT DISTINCT AGS.AgentID,AGN.Agent,e.Session

    FROM #agentsession AS AGS

    JOIN #agents AS AGN

    ON AGN.AgentID = AGS.AgentID

    --= This could as easily be a parameter if this code was in a stored proc - instead of hard-coded as 15 minutes.

    CROSS APPLY( SELECT 15 AS INTERVAL) a

    --= Figure out the dates without the time

    CROSS APPLY( SELECT

    DATEADD(DAY,0,DATEDIFF(DAY,0,AGS.sessionstart)) AS StartDay

    ,DATEADD(DAY,0,DATEDIFF(DAY,0,AGS.sessionend)) AS EndDay

    ,DATEDIFF(MINUTE,AGS.sessionstart,AGS.sessionend) AS Duration) AS b

    --= Figure out the sessionstart and sessionend rounded down to the nearest "Interval" as a datetime

    CROSS APPLY( SELECT

    DATEADD(MINUTE,FLOOR(DATEDIFF(MINUTE,b.StartDay,AGS.SessionStart)/INTERVAL)*INTERVAL,b.StartDay) StartTime

    ,DATEADD(MINUTE,FLOOR(DATEDIFF(MINUTE,b.StartDay,AGS.SessionEnd)/INTERVAL)*INTERVAL,b.EndDay) EndTime

    ) AS c

    --= Work out how many INTERVAL periods are between the start and end period

    CROSS APPLY( SELECT

    DATEDIFF(MINUTE,c.StartTime,c.EndTime)/INTERVAL AS Intervals) AS d

    --= Generate datetime values for the periods the Agent was logged in.

    CROSS APPLY( SELECT

    DATEADD(MINUTE,INTERVAL*T.N,c.StartTime) AS Session

    FROM tally T

    WHERE N<=d.Intervals ) AS e

    ORDER BY e.Session , AGN.Agent

    MM



    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • Thanks a lot everyone. I had Jeff's article marked down as something that would probably benefit this, but to be honest I haven't got around to it yet (guess what I'll be reading tonight before going to sleep? :-P)

    As for the suggestions, immediately I've added a tally table with the changes Wayne suggested. I then intend to do some testing on my real data to see the performance differences between the others.

    Celko - I know we have a TIME data type, hence why my original solution was using an on the fly table variable with times set to a 15 minute variable. Thanks anyway, but I suspect the number table approach will work better for my requirements since I need to be able to change the interval.


    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 5 posts - 1 through 5 (of 5 total)

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