SQL Server 2008 : how to select sum of all sessions where difference between two consecutive sessions is less than 10 minutes

I have a table which stores chat messages for users. Every message is logged in this table. I have to calculate chat duration for a particular user.

Since there is a possibility that user is chatting at x time and after x+10 times he leaves chatting. After X+20 time, again user starts chatting. So the time period between x+10 and x+20 should not be accounted.

Table structure and sample data is as depicted. Different color represent two chat sessions for same user. As we can see that between 663 and 662 there is a difference of more than 1 hour, so such sessions should be excluded from the resultset. Final result should be 2.33 minutes.

declare @messagetime1 as datetime
declare @messagetime2 as datetime
select @messagetime1=messagetime from tbl_chatMessages where ID=662
select @messagetime2=messagetime from tbl_chatMessages where ID=659
print datediff(second,@messagetime2,@messagetime1)
   Result --- 97 seconds

declare @messagetime3 as datetime
declare @messagetime4 as datetime
select @messagetime3=messagetime from tbl_chatMessages where ID=668
select @messagetime4=messagetime from tbl_chatMessages where ID=663
print datediff(second,@messagetime4,@messagetime3)
   Result -- 43 seconds

Please suggest a solution to calculate duration of chat. This is one of the logic I could think of, in case any one of you has a better idea. Please share with a solution

Answers


first need to calculate the gap between adjacent messages, if the gap of more than 600 seconds, so the time between these messages 0

SELECT SUM(o.duration) / 60.00 AS duration
FROM dbo.tbl_chatMessages t1
  OUTER APPLY (
               SELECT TOP 1 
                 CASE WHEN DATEDIFF(second, t2.messageTime, t1.messageTime) > 600
                      THEN 0 
                      ELSE DATEDIFF(second, t2.messageTime, t1.messageTime) END
               FROM dbo.tbl_chatMessages t2
               WHERE t1.messageTime > t2.messageTime
               ORDER BY t2.messageTime DESC
               ) o(duration)

See demo on SQLFiddle


Need Your Help

Build Cocoa application Bundle with private dylib/framework

objective-c xcode cocoa frameworks build

I use xcode 4 to build a cocoa application with a private dylib/framework.

Does svn move command also moves files within directory?

svn

I've used svn move on dir that contains sub dirs and files. But under status I see,

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.