Differences

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

--- vendors:cisco:uc:ece:sql [2023/03/16 10:26] – [Number of chats sent / receipts per chat conversation (activityId)] gerardorourke
+++ vendors:cisco:uc:ece:sql [2024/12/05 08:55] (current) – [SQL User <> CCE Mapping] gerardorourke
@@ Line 557: / Line 557: @@
 === Each Activity ID ===
 <code sql>
-SELECT [EVENT_ID]
+SELECT
-      ,[EVENT_DATE_GMT]
+[EVENT_ID]
-      ,[ACTIVITY_ID]
+,[EVENT_DATE_GMT]
-	  ,ENTRYPOINT_ID
+,[ACTIVITY_ID]
-      ,[AGENT_RESPONSE_COUNT]
+,ENTRYPOINT_ID
-      ,[CUSTOMER_MESSAGE_COUNT]
+,[AGENT_RESPONSE_COUNT]
+,[CUSTOMER_MESSAGE_COUNT]
-  FROM [eGActiveDB].[dbo].[EGLV_SESSION_CHAT_EVENT]
+FROM [eGActiveDB].[dbo].[EGLV_SESSION_CHAT_EVENT]
-  WHERE EVENT_DATE_GMT > GetDate()-30
+WHERE EVENT_DATE_GMT > GetDate()-30
+AND AGENT_RESPONSE_COUNT <> 0
-  ORDER BY EVENT_ID DESC
+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) ===
@@ Line 579: / Line 602: @@
 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>