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
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