CODEWITHSUNDEEP
sqlsql-serversql-server-2008
rohit
rohit

Reputation: 501

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.

enter image description here

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

Upvotes: 4

Views: 323

Answers (5)

JdMR
JdMR

Reputation: 1266

I’d focus on slight modifications in table structure and updating the chat server application code (if possible of course).

Can you have the chat server to generate new chat ID every time there is a delay between messages that is longer than X minutes? If yes then calculating chat duration will become very easy.

Upvotes: 0

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

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

Upvotes: 2

Bogdan Sahlean
Bogdan Sahlean

Reputation: 1

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270633

Here is the reasoning behind my solution. First, identify each chat that starts a chatting period. You can do this with a flag that identifies a chat that is more than 10 minutes from the previous chat.

Then, take this flag and do a cumulative sum. This sum actually serves as a grouping identifier for the chat periods. Finally, aggregate the results to get the info 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 challenge that I may not fully understand is how you are matching between senders and recipients. All the rows in your sample data have the same pair. This is looking at the "user" from the SenderId perspective, but takes into account that in a chat period, the user could be either the sender or recipient.

Upvotes: 0

Gidil
Gidil

Reputation: 4137

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 set up a working example on SQL Fiddle. Take a look and let me know if you have any questions.

Upvotes: 0

Related Questions