Differences

This shows you the differences between two versions of the page.

--- vendors:cisco:uc:ece:sql [2020/09/21 12:58] – [TEMP 1] gerardorourke
+++ vendors:cisco:uc:ece:sql [2024/12/05 08:55] (current) – [SQL User <> CCE Mapping] gerardorourke
@@ Line 552: / Line 552: @@
 </code>
+===== Chat SQL =====
+====Number of chats sent / receipts per chat conversation (activityId)====
+=== Each Activity ID ===
+<code sql>
+SELECT
+[EVENT_ID]
+,[EVENT_DATE_GMT]
+,[ACTIVITY_ID]
+,ENTRYPOINT_ID
+,[AGENT_RESPONSE_COUNT]
+,[CUSTOMER_MESSAGE_COUNT]
+FROM [eGActiveDB].[dbo].[EGLV_SESSION_CHAT_EVENT]
+WHERE EVENT_DATE_GMT > GetDate()-30
+AND AGENT_RESPONSE_COUNT <> 0
+AND CUSTOMER_MESSAGE_COUNT <> 0
+ORDER BY CUSTOMER_MESSAGE_COUNT DESC
+</code>
+**If you want to get the Median / Half way point - you can use SQL TOP 50 Percent**
+<code>
+SELECT TOP 50 PERCENT
+[EVENT_ID]
+,[EVENT_DATE_GMT]
+,[ACTIVITY_ID]
+,ENTRYPOINT_ID
+,[AGENT_RESPONSE_COUNT]
+,[CUSTOMER_MESSAGE_COUNT]
+FROM [eGActiveDB].[dbo].[EGLV_SESSION_CHAT_EVENT]
+WHERE EVENT_DATE_GMT > GetDate()-30
+AND AGENT_RESPONSE_COUNT <> 0
+AND CUSTOMER_MESSAGE_COUNT <> 0
+ORDER BY CUSTOMER_MESSAGE_COUNT DESC
+</code>
+=== Average Count for all Activity ID (Grouped by EntryPoint Id) ===
+<code sql>
+SELECT
+ENTRYPOINT_ID = ENTRYPOINT_ID
+,AVG_AGENT_RESPONSE_COUNT = AVG([AGENT_RESPONSE_COUNT])
+,AVG_CUSTOMER_MESSAGE_COUNT = AVG([CUSTOMER_MESSAGE_COUNT])
+FROM [eGActiveDB].[dbo].[EGLV_SESSION_CHAT_EVENT]
+WHERE EVENT_DATE_GMT > GetDate()-30
+AND AGENT_RESPONSE_COUNT <> 0
+AND CUSTOMER_MESSAGE_COUNT <> 0
+GROUP BY ENTRYPOINT_ID
+</code>
+==== SQL User <> CCE Mapping ====
+<code sql>
+SELECT *
+FROM EGICM_USER
+    LEFT JOIN EGPL_USER ON EGPL_USER.user_id = EGICM_USER.user_id
+ORDER BY FIRST_NAME, LAST_NAME
+</code>