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

Question

I have a table that stores chat messages for users. Each message is logged in this table. I need to calculate the chat duration for a specific user.

Since there is a possibility that the user talks x times, and after x + 10 times, he leaves the chat. After X + 20 times, the user starts chatting again. Thus, the time period between x + 10 and x + 20 should not be taken into account.

The table structure and sample data are shown as shown. A different color represents two chat sessions for one user. Since we see that between 663 and 662 the difference is more than 1 hour, therefore, such sessions should be excluded from the result set. The end 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 chat duration. This is one of the logic that I could think of if one of you has a better idea. Please share with the solution.

Answer 1

first you need to calculate the gap between adjacent messages, if the gap exceeds 600 seconds, so the time between these messages is 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 SQLFiddle

Answer 2

Try something like this:

 WITH DATA AS (SELECT t1.*, CASE WHEN Isnull(Datediff(MI, t2.MESSAGETIME, t1.MESSAGETIME), 11) > 10 THEN 0 ELSE 1 END first_ident FROM TABLE1 t1 LEFT JOIN TABLE1 t2 ON t1.ID = t2.ID + 1), CTE AS (SELECT ID, MESSAGETIME, ID gid, 0 AS tot_time FROM DATA WHERE FIRST_IDENT = 0 UNION ALL SELECT t1.ID, t1.MESSAGETIME, t2.GID, t2.TOT_TIME + Datediff(MI, t2.MESSAGETIME, t1.MESSAGETIME) FROM DATA t1 INNER JOIN CTE t2 ON t1.ID = t2.ID + 1 AND t1.FIRST_IDENT = 1) SELECT GID, Max(TOT_TIME) Tot_time FROM CTE GROUP BY GID 

I created a working SQL Fiddle example. Take a look and let me know if you have any questions.

Answer 3

That is the reason for my decision. First define each chat that starts a chat. You can do this with a flag that identifies the chat that is more than 10 minutes from the previous chat.

Then take this flag and make a cumulative amount. This amount actually serves as a grouping identifier for chat periods. Finally, summarize the results to get information for each chat period.

 with cmflag as ( select cm.*, (case when datediff(min, prevmessagetime, messagetime) > 10 then 0 else 1 end) as ChatPeriodStartFlag from (select cm.*, (select top 1 messagetime from tbl_chatMessages cm2 where cm2.senderId = cm.senderId or cm2.RecipientId = cm.senderId ) as prevmessagetme from tbl_chatMessages cm ) cm ), cmcum as ( select cm.*, (select sum(ChatPeriodStartFlag) from cmflag cmf where cm2.senderId = cm.senderId or cm2.RecipientId = cm.senderId and cmf.messagetime <= cm.messagetime ) as ChatPeriodGroup from tbl_chatMessages cm ) select cm.SenderId, ChatPeriodGroup, min(messageTime) as mint, max(messageTime) as maxT from cmcum group by cm.SenderId, ChatPeriodGroup; 

One problem that I may not fully understand is how you compare between senders and recipients. All rows in your sample data have the same pair. This looks at the user from the point of view of SenderId , but takes into account that during the chat period the user can be either the sender or the recipient.

Answer 4

You can use this query ( here ):

 DECLARE @Results TABLE( RowNum INT NOT NULL, senderID INT NOT NULL DEFAULT(80), recipientID INT NOT NULL DEFAULT(79), PRIMARY KEY(RowNum,senderID,recipientID), messageTime DATETIME NOT NULL ); INSERT INTO @Results(RowNum,senderID,recipientID,messageTime) SELECT ROW_NUMBER() OVER(PARTITION BY senderID,recipientID ORDER BY messageTime, ID) AS RowNum, c.senderID,c.recipientID,c.messageTime FROM dbo.tbl_chatMessages c; WITH RecursiveCTE AS( SELECT crt.RowNum,crt.senderID,crt.recipientID, crt.messageTime, 1 AS SessionID FROM @Results crt WHERE crt.RowNum=1 UNION ALL SELECT crt.RowNum,crt.senderID,crt.recipientID, crt.messageTime, CASE WHEN DATEDIFF(MINUTE,prev.messageTime,crt.messageTime) <= 10 THEN prev.SessionID ELSE prev.SessionID+1 END FROM @Results crt INNER JOIN RecursiveCTE prev ON crt.RowNum=prev.RowNum+1 AND crt.senderID=prev.senderID AND crt.recipientID=prev.recipientID ) SELECT *, STUFF(CONVERT(VARCHAR(8), DATEADD(SECOND,x.SessionDuration,0), 114), 1,3,'') AS SessionDuration_mmss, SUM(x.SessionDuration) OVER() AS SessionDuration_Overall, STUFF(CONVERT(VARCHAR(8), DATEADD(SECOND,SUM(x.SessionDuration) OVER(),0), 114), 1,3,'') AS SessionDuration_Overall_mmss FROM( SELECT r.senderID,r.recipientID,r.SessionID, DATEDIFF(SECOND, MIN(r.messageTime),MAX(r.messageTime)) AS SessionDuration FROM RecursiveCTE r GROUP BY r.senderID,r.recipientID,r.SessionID ) x OPTION(MAXRECURSION 0); 

Results:

 senderID recipientID SessionID SessionDuration SessionDuration_mmss SessionDuration_Overall SessionDuration_Overall_mmss -------- ----------- ----------- --------------- -------------------- ----------------------- ---------------------------- 80 79 1 97 01:37 140 02:20 80 79 2 43 00:43 140 02:20 

Answer 5

I'd focus on small changes in the table structure and updating the chat server application code (if possible, of course).

Do you have a chat server to generate a new chat ID every time there is a delay between messages that are longer than X minutes? If so, calculating the duration of the chat will become very simple.