Member Avatar for jacob21

Chats table maintain chat history b/w users and agent.Type(0,1,2) indicates start and end point of chat by user/agent. Please suggest how we cam give each chat a unique session For Example Chat b/w company_id(5969) and user_id(67) has 1 unique chat id. Not Sure Is it possible in query or have to use any procedure/Cursor. Please suggest

Chats(id,type,created_at,company_id,User_id)

    12345,0,'2015-02-01 6:00:09',5969,67 // 0 :type Chat Started by User 
    12347,0,'2015-02-01 6:02:09',5969,67
    12348,1,'2015-02-01 6:03:09',5969,67 // 1 type: Chat Started by Agent
    12355,0,'2015-02-01 6:04:09',5969,67
    12358,0,'2015-02-01 6:05:09',5969,67
    12362,2,'2015-02-01 6:06:09',5969,67 // 2 type: Chat Closed by Agent

    14345,0,'2015-02-01 6:00:09',5969,67 // 0 :type Chat Again Started by User for same user and company
    14347,0,'2015-02-01 6:02:09',5969,67
    14348,1,'2015-02-01 6:03:09',5969,67 // 1 type: Chat Started by Agent
    14355,0,'2015-02-01 6:04:09',5969,67
    14358,0,'2015-02-01 6:05:09',5969,67
    14362,2,'2015-02-01 6:06:09',5969,67 // 2 type: Chat Closed by Agent






   1,12345,0,'2015-02-01 6:00:09',5969,67 // 0 :type Chat Started by User 
        1,12347,0,'2015-02-01 6:02:09',5969,67
        1,12348,1,'2015-02-01 6:03:09',5969,67 // 1 type: Chat Started by Agent
        1,12355,0,'2015-02-01 6:04:09',5969,67
        1,12358,0,'2015-02-01 6:05:09',5969,67
        1,12362,2,'2015-02-01 6:06:09',5969,67 // 2 type: Chat Closed by Agent
  • 2 Contributors
  • 1 Reply
  • 221 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by AleMonteiro
Member Avatar for AleMonteiro

I'm not sure... but maybe this could do the trick:

SET @session=0;
SELECT  DISTINCT  @session:=@session+1, company_id, user_id 
FROM chats
ORDER BY created_at
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.