Time difference between entries

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Time difference between entries

Author

Topic

knox203
Starting Member

31 Posts

Posted - 2009-11-16 : 14:09:47

Hello all,

I have a simple query which grabs all chat sessions for the day. That is all fine and well, but I'd like to find the difference (in seconds) between each message, and remove that block of time from the total chat session if the difference is more than 5 minutes but leave the rest.

Here's the query:

SELECT C.[SessionIdTime], C.[Date]
FROM   LcsLog.dbo.ConferenceMessages AS C INNER JOIN
       LcsLog.dbo.Users AS U ON C.FromId = U.UserId
WHERE  (DATEADD(hh, -8, C.SessionIdTime) BETWEEN CONVERT(VARCHAR, GETDATE(), 101) AND CONVERT(VARCHAR, DATEADD(d, 1, GETDATE()), 101))
ORDER BY C.[SessionIdTime], C.[Date]

Here's the results:

SessionIdTime           Date
2009-11-16 15:58:27.610	2009-11-16 15:58:27.610
2009-11-16 16:03:05.767	2009-11-16 16:03:05.767
2009-11-16 16:03:05.767	2009-11-16 16:29:38.920
2009-11-16 16:03:05.767	2009-11-16 16:48:00.860
2009-11-16 16:03:05.767	2009-11-16 16:53:36.030
2009-11-16 16:03:05.767	2009-11-16 16:54:27.280
2009-11-16 16:18:45.827	2009-11-16 16:18:45.827
2009-11-16 16:18:45.827	2009-11-16 17:08:32.517
2009-11-16 16:18:45.827	2009-11-16 17:13:43.843
2009-11-16 16:20:50.437	2009-11-16 16:20:50.437
2009-11-16 16:20:50.437	2009-11-16 16:22:09.813
2009-11-16 16:25:00.967	2009-11-16 16:25:00.967
2009-11-16 16:28:24.250	2009-11-16 16:28:24.250
2009-11-16 16:31:47.657	2009-11-16 16:31:47.657
2009-11-16 16:31:47.657	2009-11-16 16:31:50.000
2009-11-16 16:31:47.657	2009-11-16 16:32:30.657
2009-11-16 16:31:47.657	2009-11-16 16:35:10.733

I'm not sure where to approach this task. I've thought about doing it in PHP but I figured it might be less code and less strain on our web server to just do it in my SQL query. Thank you all for your time.

- Adam

EDIT: Sorry, forgot to point out that the 'Date' column is the message sent timestamp.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-11-16 : 14:29:29

You haven't actually given us enough info about your tables to answer your question..DDLs and sample data would be helpful
but this should get you started.

Jim

DECLARE @Table TABLE (SessionTime datetime,Date datetime)

INSERT INTO @table
SELECT '2009-11-16 15:58:27.610','2009-11-16 15:58:27.610' UNION ALL
SELECT '2009-11-16 16:03:05.767','2009-11-16 16:03:05.767' UNION ALL
SELECT '2009-11-16 16:03:05.767','2009-11-16 16:29:38.920' UNION ALL
SELECT '2009-11-16 16:03:05.767','2009-11-16 16:48:00.860' UNION ALL
SELECT '2009-11-16 16:03:05.767','2009-11-16 16:53:36.030' UNION ALL
SELECT '2009-11-16 16:03:05.767','2009-11-16 16:54:27.280' UNION ALL
SELECT '2009-11-16 16:18:45.827','2009-11-16 16:18:45.827' UNION ALL
SELECT '2009-11-16 16:18:45.827','2009-11-16 17:08:32.517' UNION ALL
SELECT '2009-11-16 16:18:45.827','2009-11-16 17:13:43.843' UNION ALL
SELECT '2009-11-16 16:20:50.437','2009-11-16 16:20:50.437' UNION ALL
SELECT '2009-11-16 16:20:50.437','2009-11-16 16:22:09.813' UNION ALL
SELECT '2009-11-16 16:25:00.967','2009-11-16 16:25:00.967' UNION ALL
SELECT '2009-11-16 16:28:24.250','2009-11-16 16:28:24.250' UNION ALL
SELECT '2009-11-16 16:31:47.657','2009-11-16 16:31:47.657' UNION ALL
SELECT '2009-11-16 16:31:47.657','2009-11-16 16:31:50.000' UNION ALL
SELECT '2009-11-16 16:31:47.657','2009-11-16 16:32:30.657' UNION ALL
SELECT '2009-11-16 16:31:47.657','2009-11-16 16:35:10.733'

;with Cte_Times(RowNumber,SessionTime)
AS
(
SELECT [RowNumber] = ROW_Number() over(order by sessiontime asc),sessiontime
FROM @table
)

select distinct t1.sessiontime--,datediff(mi,t1.sessiontime,t2.sessiontime)
from Cte_Times t1
inner join
Cte_Times t2
on
t1.rownumber = t2.rownumber - 1
WHERE
datediff(mi,t1.sessiontime,t2.sessiontime) <5

Everyday I learn something that somebody else already knew

knox203
Starting Member

31 Posts

Posted - 2009-11-16 : 15:33:57

jimf,

Thank you very much for your response. I was able to create a solution in a way that is more familiar to me thanks to your code. I appreciate the time you spent to write that all out!

- Adam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources