Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Time Intervals
Add to briefcase

Time Intervals Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Cadavre
Posted Monday, November 22, 2010 7:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486
This is driving me slowly insane. . .

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

Not a DBA, just 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

LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1024349
WayneS
Posted Monday, November 22, 2010 9:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:12 AM
Points: 6,370, Visits: 8,235
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
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
Post #1024461
LutzM
Posted Monday, November 22, 2010 10:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131
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
Post #1024470
CELKO
Posted Monday, November 22, 2010 4:17 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
We 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 Timeslots
VALUES (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.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1024767
mister.magoo
Posted Monday, November 22, 2010 5:17 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:50 PM
Points: 1,308, Visits: 3,899
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
Post #1024777
Cadavre
Posted Tuesday, November 23, 2010 3:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:16 AM
Points: 2,236, Visits: 6,486
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? )

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.

Not a DBA, just 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

LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1024956
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.