Time Intervals

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  November 22, 2010 at 7:56 am

  #243080

  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/

  ---

• WayneS

  SSC Guru

  Points: 95409

  More actions

  November 22, 2010 at 9:48 am

  #1253845

  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

• LutzM

  SSC Guru

  Points: 107049

  More actions

  November 22, 2010 at 10:01 am

  #1253853

  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]

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  November 22, 2010 at 5:17 pm

  #1254120

  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]