Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Time Intervals


Time Intervals

Author
Message
Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2598 Visits: 8437
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

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
WayneS
WayneS
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6271 Visits: 10404
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7021 Visits: 13559
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
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2288 Visits: 7833
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
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Cadavre
    Cadavre
    SSCrazy
    SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

    Group: General Forum Members
    Points: 2598 Visits: 8437
    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

    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/



    If you litter your database queries with nolock query hints, are you aware of the side effects?
    Try reading a few of these links...

    (*) Missing rows with nolock
    (*) Allocation order scans with nolock
    (*) Consistency issues with nolock
    (*) Transient Corruption Errors in SQL Server error log caused by nolock
    (*) Dirty reads, read errors, reading rows twice and missing rows with nolock


    Craig Wilkinson - Software Engineer
    LinkedIn
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search