InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 05:31:08 GMT20http://www.sqlservercentral.com/Forums/Topic1024349-392-1.aspxThanks 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.Tue, 23 Nov 2010 03:14:39 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1024349-392-1.aspxBearing 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.[code="sql"];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 AGSJOIN #agents AS AGNON 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 timeCROSS 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 datetimeCROSS 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 periodCROSS 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 eORDER BY e.Session , AGN.Agent[/code]Mon, 22 Nov 2010 17:17:34 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1024349-392-1.aspxWe now have a TIME data type, so you can set up a report range table:CREATE TABLE Timeslots(timeslot_nbr INTEGER NOT NULL PRIMARY KEY, slot_start_time TIME NOT NULL, slot_end_time TIME NOT NULL, etc);INSERT INTO TimeslotsVALUES (1, '00:00:00', '00:15:00'), (2, '00:15:01', '00:30:00'), (3, '00:30:01', '00:45:00'), ..;Now, you can use CAST (my_datetime AS TIME) BETWEEN slot_start_time AND slot_end_time Play with the precision, if you need better than a second. Mon, 22 Nov 2010 16:17:33 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1024349-392-1.aspxI'd use a TallyTable based method:[code="sql"]DECLARE @day DATETIME, @interval INTSET @interval = 15SET @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 splitTimeFROM #AgentSession sess LEFT OUTER JOIN #Agents agent ON sess.agentid = agent.agentid CROSS JOIN splitTimes spWHERE sess.sessionstart >= @day -- 20101122 00:00 AND sess.sessionend < DateAdd(hour, 20, DateAdd(minute, 1, @day)) -- 20101122 20:01ORDER BY sp.splitTime[/code]Mon, 22 Nov 2010 10:01:45 GMTLutzMhttp://www.sqlservercentral.com/Forums/Topic1024349-392-1.aspxThe only thing I'm seeing is the way that you populate your @splitTimes table variable. I'd change it to a set-based method:[code="sql"]-- 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 @splitTimesSELECT [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[/code]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:[code="sql"]WHERE sess.sessionstart >= <your starting date> AND sess.sessionend < <your ending date + 1 interval)[/code]In the case of what you're using:[code="sql"]WHERE sess.sessionstart >= @day -- 20101122 00:00 AND sess.sessionend < DateAdd(hour, 20, DateAdd(minute, 1, @day)) -- 20101122 20:01[/code]Mon, 22 Nov 2010 09:48:35 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1024349-392-1.aspxThis 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)[code="sql"]DROP TABLE #AgentsGOCREATE 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 #AgentSessionGOCREATE 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 #AgentSessionSET sessionend = DATEADD(MI,ROUND(((60)*RAND()),0),sessionstart)[/code]In the first few minutes, I came up with a way to achieve what is required, using the following: -[code="sql"]DECLARE @splitter TIME, @day DATETIME, @interval INTSET @splitter = '00:00'SET @interval = 15SET @day = '2010-11-22'DECLARE @splitTimes TABLE (splitTime TIME )INSERT INTO @splitTimes(splitTime)SELECT @splitterSET @splitter = Dateadd(MINUTE, @interval, @splitter)WHILE @splitter <= '20:00' BEGININSERT INTO @splitTimes (splitTime)SELECT @splitterSET @splitter = Dateadd(MINUTE, @interval, @splitter)END /* End While Loop */SELECT agent.Agent, sp.splitTimeFROM #AgentSession sess WITH(nolock)LEFT OUTER JOIN #Agents agent WITH(nolock) ON sess.agentid = agent.agentidCROSS JOIN @splitTimes spWHERE 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[/code]--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 ;-)Mon, 22 Nov 2010 07:56:09 GMTCadavre