Table of Contents

ECE SQL

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT 
CREATED = EGPL_ROUTING_QUEUE.WHEN_CREATED,
QUEUE_NAME = EGPL_ROUTING_QUEUE.QUEUE_NAME,
QUEUE_ID= EGPL_ROUTING_QUEUE.QUEUE_ID,
ACTIVITY_ID  = EGPL_CASEMGMT_ACTIVITY.ACTIVITY_ID,
QUEUE_STATE = EGPL_ROUTING_QUEUE.QUEUE_STATE,
CASE EGPL_ROUTING_QUEUE.QUEUE_STATE 
WHEN '0' THEN 'Inactive'
WHEN '1' THEN 'Active'
ELSE 'unknown'
END AS QUEUE_STATE_NAME
--,EGPL_CASEMGMT_ACTIVITY.*
--,EGPL_ROUTING_QUEUE.*
 
FROM     
 EGPL_CASEMGMT_ACTIVITY 
,EGPL_ROUTING_QUEUE
 
WHERE 
EGPL_CASEMGMT_ACTIVITY.QUEUE_ID = EGPL_ROUTING_QUEUE.QUEUE_ID
--AND EGPL_ROUTING_QUEUE.QUEUE_NAME = 'Default exception queue'

SQL used by ECE to pull by routing domain info in 'import from CCE'

EXEC Sp_executesql 
N'
 
Select distinct 
tmrd.MRDomainID, 
tmrd.EnterpriseName, 
tdn.DialedNumberID,
tdn.DialedNumberString 
from 
t_Media_Routing_
Domain tmrd, 
t_Media_Class tmcl, 
t_Application_Instance tai, 
t_Application_Path tap, 
t_Application_Path_Member tapm , 
t_Dialed_Number tdn 
where tai.EnterpriseName = @P0 
and tai.ApplicationInstanceID = tap.ApplicationInstanceID 
and tmrd.MediaClassID != 4 and tap.ApplicationPathID = tapm.ApplicationPathID 
and tapm.MRDomainID = tmrd.MRDomainID 
and tmrd.MediaClassID = tmcl.MediaClassID 
and tmrd.MediaClassID = tmcl.MediaClassID 
and tmcl.EnterpriseName IN (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10) 
and tdn.MRDomainID = tmrd.MRDomainID AND tdn.Deleted = ''N''
order by tmrd.EnterpriseName, tdn.DialedNumberString                                                                                       '
, 
N'@P0 nvarchar(4000),
@P1 nvarchar(4000),
@P2 nvarchar(4000),
@P3 nvarchar(4000),
@P4 nvarchar(4000),
@P5 nvarchar(4000),
@P6 nvarchar(4000),
@P7 nvarchar(4000),
@P8 nvarchar(4000),
@P9 nvarchar(4000),
@P10 nvarchar(4000)'
, 
N'MultiChannel', 
N'Cisco_Voice', 
N'ECE_Chat', 
N'ECE_Email', 
N'ECE_Outbound', 
N'ECE_Outbound', 
N'ECE_Outbound', 
N'ECE_Outbound', 
N'ECE_Outbound', 
N'ECE_Outbound', 
N'ECE_Outbound'

Response 

MRDomainID EnterpriseName DialedNumberID DialedNumberString 
ECE_Chat sales.chat	
ECE_Email ecedemo1.purplepi.email	
ECE_Email uclab01.email	
ECE_Outbound ece.outbound 
y
SELECT DISTINCT 
tmrd.MRDomainID, 
tmrd.EnterpriseName, 
tdn.DialedNumberID,
tdn.DialedNumberString 
 
FROM 
t_Media_Routing_Domain tmrd, 
t_Media_Class tmcl, 
t_Application_Instance tai, 
t_Application_Path tap, 
t_Application_Path_Member tapm , 
t_Dialed_Number tdn 
WHERE 
--taiEnterpriseName = @P0 and 
tai.ApplicationInstanceID = tap.ApplicationInstanceID AND 
tmrd.MediaClassID != 4 AND 
tap.ApplicationPathID = tapm.ApplicationPathID AND 
tapm.MRDomainID = tmrd.MRDomainID AND 
tmrd.MediaClassID = tmcl.MediaClassID AND 
tmrd.MediaClassID = tmcl.MediaClassID AND
--tmclEnterpriseName IN (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10) and 
tdn.MRDomainID = tmrd.MRDomainID AND 
tdn.Deleted = 'N' 
/****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [ACTIVITY_ID]
      ,[CASE_ID]
      ,A.[DEPARTMENT_ID]
	  ,D.[DEPARTMENT_NAME]
      ,[ACTIVITY_MODE]
	  ,ActivityModeName = CASE(A.ACTIVITY_MODE)
	  WHEN '100' THEN 'Inbound'
	  WHEN '200' THEN 'Outbound'
	  WHEN '300' THEN 'Others'
	  ELSE CAST(ACTIVITY_MODE AS VARCHAR(50))
	  END
      ,[ACTIVITY_TYPE]
	  ,ActivityTypeName = CASE(A.ACTIVITY_TYPE)
	  WHEN '1' THEN 'Email'
	  WHEN '2000' THEN 'Chat'
	  WHEN '8000' THEN 'Web Activity'
	  WHEN '12000' THEN 'CallTrack'
	  WHEN '20000' THEN 'Social'
	  ELSE CAST(ACTIVITY_TYPE AS VARCHAR(50))
	  END
      ,[ACTIVITY_SUB_TYPE]
	  ,ACTIVITY_SUB_TYPE_Name =  CASE(A.ACTIVITY_SUB_TYPE)
	  WHEN '1' THEN 'General'
	  WHEN '2' THEN 'Webform'
	  WHEN '3' THEN 'Secure'
	  WHEN '4' THEN 'Permanent undeliverable'
	  WHEN '5' THEN 'Temporary undeliverable'
	  WHEN '6' THEN 'Reply'
	  WHEN '7' THEN 'Forwarded'
	  WHEN '8' THEN 'Composed'
	  WHEN '9' THEN 'Auto reply'
	  WHEN '10' THEN 'Auto acknoledgement'
	  WHEN '11' THEN 'Group reply'
	  WHEN '12' THEN 'Redirected'
	  WHEN '13' THEN 'Undispatchable'
	  WHEN '14' THEN 'Suervised and accepted'
	  WHEN '15' THEN 'Supervised and rejected'
	  WHEN '16' THEN 'Supervisoed and resubmitted'
	  WHEN '2001' THEN 'Chat - General'
	  WHEN '2002' THEN 'Chat - Video chat'
	  WHEN '8001' THEN 'Web - General'
	  ELSE CAST(ACTIVITY_SUB_TYPE AS VARCHAR(50))
	  END
      ,[ACTIVITY_STATUS]
      ,[ACTIVITY_STATUS_NAME] = CASE(A.ACTIVITY_STATUS)
	  WHEN '1000' THEN 'New'
	  WHEN '2000' THEN 'Pre-Workflow'
	  WHEN '3000' THEN 'Workflow'
	  WHEN '4000' THEN 'Assignment'
	  WHEN '5000' THEN 'Assigned'
	  WHEN '7000' THEN 'Pre-completion'
	  WHEN '9000' THEN 'Completed'
	  ELSE CAST(A.ACTIVITY_STATUS AS VARCHAR(50))
	  END
 
      ,[ACTIVITY_SUB_STATUS]
      ,[ACTIVITY_PRIORITY]
      ,A.[WHEN_CREATED]
      ,A.[WHO_CREATED]
	  ,WHO_CREATED_NAME = Concat(U1.FIRST_NAME, ' ', U1.LAST_NAME)
      ,A.[WHEN_MODIFIED]
      ,[DUE_DATE]
      ,[USER_LAST_WORKED]
      ,[ASSIGNED_TO]
      ,[ASSIGNED_TO_NAME] = Concat(U2.FIRST_NAME, ' ', U2.LAST_NAME)
      ,[SUBJECT]
      ,[DESCRIPTION]
      ,[LANGUAGE_ID]
      ,[CUSTOMER_ID]
      ,[CONTACT_PERSON_ID]
      ,A.[QUEUE_ID]
	  ,Q.QUEUE_NAME
      ,[CONTACT_POINT_ID]
      ,[CONTACT_POINT_DATA]
      ,[LAST_ACTION_REASON]
      ,[PINNED]
      ,[LOCKED]
      ,[ACTIVITY_ACCESS]
      ,[FOLDER_ID]
      ,[LAST_DEPARTMENT_ID]
      ,[SAVE_DRAFT_FLAG]
      ,[LEAVE_OPEN_FLAG]
      ,[NUM_NOTES]
      ,[NUM_ATTACHMENTS]
      ,[CASE_TYPE]
      ,A.[DELETE_FLAG]
      ,[CONFERENCE_FLAG]
      ,[IS_ESCALATED]
      ,A.[CREATE_DATE]
      ,A.[UPDATE_VERSION]
      ,[OUTBOUND_FAILED]
      ,[VISITOR_SESSION_ID]
      ,[VISITOR_USER_ID]
      ,[CUST_ACCOUNT_ID]
      ,[DELAY_TIME_IN_MIN]
      ,[ISSUE_TYPE_ID]
      ,[IS_AUTHENTICATED]
      ,[alias]
  FROM 
  [EGPL_CASEMGMT_ACTIVITY] A
  LEFT JOIN EGPL_DEPARTMENT D ON A.DEPARTMENT_ID = D.DEPARTMENT_ID
  LEFT JOIN EGPL_USER U1 ON U1.USER_ID = A.WHO_CREATED
  LEFT JOIN EGPL_USER U2 ON U2.USER_ID = A.ASSIGNED_TO
  LEFT JOIN EGPL_ROUTING_QUEUE Q ON Q.QUEUE_ID = A.QUEUE_ID

TEMP

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT 
       [Agent Name] = Concat(U2.FIRST_NAME, ' ', U2.LAST_NAME)
	  ,ACTIVITY_SUB_TYPE_Name =  CASE(A.ACTIVITY_SUB_TYPE)
	  WHEN '1' THEN 'General'
	  WHEN '2' THEN 'Webform'
	  WHEN '3' THEN 'Secure'
	  WHEN '4' THEN 'Permanent undeliverable'
	  WHEN '5' THEN 'Temporary undeliverable'
	  WHEN '6' THEN 'Reply'
	  WHEN '7' THEN 'Forwarded'
	  WHEN '8' THEN 'Composed'
	  WHEN '9' THEN 'Auto reply'
	  WHEN '10' THEN 'Auto acknoledgement'
	  WHEN '11' THEN 'Group reply'
	  WHEN '12' THEN 'Redirected'
	  WHEN '13' THEN 'Undispatchable'
	  WHEN '14' THEN 'Suervised and accepted'
	  WHEN '15' THEN 'Supervised and rejected'
	  WHEN '16' THEN 'Supervisoed and resubmitted'
	  WHEN '2001' THEN 'Chat - General'
	  WHEN '2002' THEN 'Chat - Video chat'
	  WHEN '8001' THEN 'Web - General'
	  ELSE CAST(ACTIVITY_SUB_TYPE AS VARCHAR(50))
	  END
      ,[Agent Name] = U2.USER_NAME
 
      ,[Alias] = A.alias
	  ,[Department Name] = D.[DEPARTMENT_NAME]
	  ,[Queue Name] = Q.QUEUE_NAME
	  ,Incoming='0'
	  ,[Incoming (Existing)]='0'
	  ,[Manual Replies]='0'
	  ,[Completed]='1'
	  ,[Composed]='0'
	  ,[Actvity Duration]='0'
	  ,[Avg FIRST Manual Reply TIME] = '00:00:00'
  FROM 
  [EGPL_CASEMGMT_ACTIVITY] A
  LEFT JOIN EGPL_DEPARTMENT D ON A.DEPARTMENT_ID = D.DEPARTMENT_ID
  LEFT JOIN EGPL_USER U2 ON U2.USER_ID = A.ASSIGNED_TO
  LEFT JOIN EGPL_ROUTING_QUEUE Q ON Q.QUEUE_ID = A.QUEUE_ID
 
 
WHERE A.ACTIVITY_STATUS = '9000'  
 
GROUP BY Concat(U2.FIRST_NAME, ' ', U2.LAST_NAME),A.ACTIVITY_SUB_TYPE,U2.USER_NAME, A.alias, D.[DEPARTMENT_NAME],Q.QUEUE_NAME

ECE Activity Report

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT 
[ACTIVITY_ID] = [A].[ACTIVITY_ID]
,[CASE_ID] = [A].[CASE_ID]
,[DEPARTMENT_ID] = [A].[DEPARTMENT_ID]
,[DEPARTMENT_NAME] = D.[DEPARTMENT_NAME]
,[ACTIVITY_MODE] = [A].[ACTIVITY_MODE]
,[ACTIVITY_MODE_NAME] = CASE([A].[ACTIVITY_MODE])
	WHEN '100' THEN 'Inbound'
	WHEN '200' THEN 'Outbound'
	WHEN '300' THEN 'Others'
    ELSE CAST([A].[ACTIVITY_MODE] AS VARCHAR(50))
	END
,[ACTIVITY_TYPE] = [A].[ACTIVITY_TYPE]
,[ACTIVITY_TYPE_NAME] = CASE([A].[ACTIVITY_TYPE])
	WHEN '1' THEN 'Email'
	WHEN '2000' THEN 'Chat'
	WHEN '8000' THEN 'Web Activity'
	WHEN '10000' THEN 'Task'
	WHEN '12000' THEN 'CallTrack'
	WHEN '20000' THEN 'Social'
    ELSE CAST([A].[ACTIVITY_TYPE] AS VARCHAR(50))
	END
,[ACTIVITY_SUB_TYPE] = [A].[ACTIVITY_SUB_TYPE]
,[ACTIVITY_SUB_TYPE_NAME] =  CASE([A].[ACTIVITY_SUB_TYPE])
	  WHEN '1' THEN 'General'
	  WHEN '2' THEN 'Webform'
	  WHEN '3' THEN 'Secure'
	  WHEN '4' THEN 'Permanent undeliverable'
	  WHEN '5' THEN 'Temporary undeliverable'
	  WHEN '6' THEN 'Reply'
	  WHEN '7' THEN 'Forwarded'
	  WHEN '8' THEN 'Composed'
	  WHEN '9' THEN 'Auto reply'
	  WHEN '10' THEN 'Auto acknoledgement'
	  WHEN '11' THEN 'Group reply'
	  WHEN '12' THEN 'Redirected'
	  WHEN '13' THEN 'Undispatchable'
	  WHEN '14' THEN 'Suervised and accepted'
	  WHEN '15' THEN 'Supervised and rejected'
	  WHEN '16' THEN 'Supervisoed and resubmitted'
	  WHEN '2001' THEN 'Chat - General'
	  WHEN '2002' THEN 'Chat - Video chat'
	  WHEN '8001' THEN 'Web - General'
	  ELSE CAST([A].[ACTIVITY_SUB_TYPE] AS VARCHAR(50))
	  END
,[ACTIVITY_STATUS] = [A].[ACTIVITY_STATUS]
,[ACTIVITY_STATUS_NAME] = CASE([A].[ACTIVITY_STATUS])
    WHEN '1000' THEN 'New' 
	WHEN '2000' THEN 'Pre-workflow'
	WHEN '3000' THEN 'Workflow'
	WHEN '4000' THEN 'Assignment'
	WHEN '5000' THEN 'Assigned'
	WHEN '7000' THEN 'Pre-completion'
	WHEN '9000' THEN 'Completed'
    ELSE CAST([A].[ACTIVITY_STATUS] AS VARCHAR(50))
	END
,[ACTIVITY_SUB_STATUS] = [A].[ACTIVITY_SUB_STATUS]
,[ACTIVITY_SUB_STATUS_NAME] = CASE([A].[ACTIVITY_SUB_STATUS])
    WHEN '1900' THEN 'In Progress' 
    WHEN '2100' THEN 'Ready for custom processing' 
    WHEN '2200' THEN 'Ready for virus Scanning' 
    WHEN '2300' THEN 'In progress' 
    WHEN '3100' THEN 'Ready for inbound workflow' 
    WHEN '3200' THEN 'Ready for outbound workflow' 
    WHEN '3300' THEN 'Ready for general workflow' 
    WHEN '3400' THEN 'Ready for transfer workflow' 
    WHEN '3800' THEN 'Error' 
    WHEN '3900' THEN 'In progress' 
    WHEN '4100' THEN 'Ready for internal assignment' 
    WHEN '4105' THEN 'Queued for Unified CCE Routing. Waiting for Assignment' 
    WHEN '4107' THEN 'Queued for ECE Supervisory assignment. Waiting for Assignment to Supervisor by ECE' 
    WHEN '4200' THEN 'Waiting for External Assignment - Telephony Queue' 
    WHEN '4300' THEN 'Error' 
    WHEN '4400' THEN 'Scheduled' 
    WHEN '4900' THEN 'In Progress' 
    WHEN '5100' THEN 'New - Unread' 
    WHEN '5200' THEN 'Pending' 
    WHEN '5300' THEN 'Wrap Up' 
    WHEN '5800' THEN 'Error' 
    WHEN '5900' THEN 'In Progress' 
    WHEN '7100' THEN 'Ready for email dispatch' 
    WHEN '7200' THEN 'Ready for fax dispatch' 
    WHEN '7300' THEN 'Email dispatch in progress' 
    WHEN '7800' THEN 'Error' 
    WHEN '7900' THEN 'In Progress' 
    WHEN '9100' THEN 'Done' 
    WHEN '9200' THEN 'Abandoned Chat' 
	ELSE CAST([A].[ACTIVITY_SUB_STATUS] AS VARCHAR(50))
	END
 
,[ACTIVITY_PRIORITY] = [A].[ACTIVITY_PRIORITY]
,[WHEN_CREATED] = [A].[WHEN_CREATED]
,[WHO_CREATED] = [A].[WHO_CREATED]
,[WHO_CREATED_NAME] = Concat(U1.FIRST_NAME, ' ', U1.LAST_NAME)
,[WHO_CREATED_USERNAME] = U1.USER_NAME
,[WHEN_MODIFIED] = [A].[WHEN_MODIFIED]
,[DUE_DATE] = [A].[DUE_DATE]
,[USER_LAST_WORKED]= [A].[USER_LAST_WORKED]
,[USER_LAST_WORKED_CREATED_NAME] = Concat(U3.FIRST_NAME, ' ', U3.LAST_NAME)
,[USER_LAST_WORKED_CREATED_USERNAME] = U3.USER_NAME
,[Queue Name] = Q.QUEUE_NAME
,[ASSIGNED_TO] = [A].[ASSIGNED_TO]
,[ASSIGNED_TO_NAME] = Concat(U2.FIRST_NAME, ' ', U2.LAST_NAME)
,[ASSIGNED_TO_USERNAME] = U2.USER_NAME
,[SUBJECT] = [A].[SUBJECT]
,[DESCRIPTION] = [A].[DESCRIPTION]
,[LANGUAGE_ID] = [A].[LANGUAGE_ID]
,[CUSTOMER_ID] = [A].[CUSTOMER_ID]
,[CONTACT_PERSON_ID] = [A].[CONTACT_PERSON_ID]
,[CONTACT_PERSON_NAME] = CONCAT([P].[FIRST_NAME],' ',[P].[LAST_NAME])
,[QUEUE_ID] = [A].[QUEUE_ID]
,[CONTACT_POINT_ID]= [A].[CONTACT_POINT_ID]
,[CONTACT_POINT_DATA] = [A].[CONTACT_POINT_DATA]
,[LAST_ACTION_REASON] = [A].[LAST_ACTION_REASON]
,[PINNED] = [A].[PINNED]
,[LOCKED] = [A].[LOCKED]
,[ACTIVITY_ACCESS] = [A].[ACTIVITY_ACCESS]
,[FOLDER_ID] = [A].[FOLDER_ID]
,[LAST_DEPARTMENT_ID] = [A].[LAST_DEPARTMENT_ID]
,[SAVE_DRAFT_FLAG] = [A].[SAVE_DRAFT_FLAG]
,[LEAVE_OPEN_FLAG] = [A].[LEAVE_OPEN_FLAG]
,[NUM_NOTES] = [A].[NUM_NOTES]
,[NUM_ATTACHMENTS] = [A].[NUM_ATTACHMENTS]
,[CASE_TYPE] = [A].[CASE_TYPE]
,[DELETE_FLAG] = [A].[DELETE_FLAG]
,[CONFERENCE_FLAG] = [A].[CONFERENCE_FLAG]
,[IS_ESCALATED] = [A].[IS_ESCALATED]
,[CREATE_DATE] = [A].[CREATE_DATE]
,[UPDATE_VERSION] = [A].[UPDATE_VERSION]
,[OUTBOUND_FAILED] = [A].[OUTBOUND_FAILED]
,[VISITOR_SESSION_ID] = [A].[VISITOR_SESSION_ID]
,[VISITOR_USER_ID] = [A].[VISITOR_USER_ID]
,[CUST_ACCOUNT_ID] = [A].[CUST_ACCOUNT_ID]
,[DELAY_TIME_IN_MIN] = [A].[DELAY_TIME_IN_MIN]
,[ISSUE_TYPE_ID] = [A].[ISSUE_TYPE_ID]
,[IS_AUTHENTICATED]= [A].[IS_AUTHENTICATED]
,[Alias] = [A].[alias]
 
  FROM 
  [EGPL_CASEMGMT_ACTIVITY] A
  LEFT JOIN EGPL_DEPARTMENT D ON A.DEPARTMENT_ID = D.DEPARTMENT_ID
  LEFT JOIN EGPL_USER U1 ON U1.USER_ID = A.WHO_CREATED
  LEFT JOIN EGPL_USER U2 ON U2.USER_ID = A.ASSIGNED_TO
  LEFT JOIN EGPL_USER U3 ON U3.USER_ID = A.USER_LAST_WORKED
  LEFT JOIN EGPL_ROUTING_QUEUE Q ON Q.QUEUE_ID = A.QUEUE_ID
  LEFT JOIN EGPL_CASEMGMT_CUSTOMER C ON C.CUSTOMER_ID = A.CUSTOMER_ID
  LEFT JOIN EGPL_CASEMGMT_CONTACT_PERSON P ON P.CONTACT_PERSON_ID = A.CONTACT_PERSON_ID

CASE Report

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
SELECT 
 
  [CASE_ID] = [C].[CASE_ID]
 ,[CASE_STATUS] = [C].[CASE_STATUS]
 ,[CASE_STATUS_NAME] = CASE([C].[CASE_STATUS])
  WHEN '0' THEN 'Open'
  WHEN '1' THEN 'Closed'
  WHEN '2' THEN 'Ready to be Closed'
  ELSE CAST([C].[CASE_STATUS] AS VARCHAR(2))
  END
 ,[CASE_GROUP_ID] = [C].[CASE_GROUP_ID]
 ,[DEPARTMENT_ID] = [C].[DEPARTMENT_ID]
 ,[DEPARTMENT_NAME] = [D].[DEPARTMENT_NAME]
 ,[ORIGINAL_SOURCE] = [C].[ORIGINAL_SOURCE]
 ,[WHEN_CREATED] = [C].[WHEN_CREATED]
 ,[WHO_CREATED] = [C].[WHO_CREATED]
 ,[WHO_CREATED_NAME] = Concat(U1.FIRST_NAME, ' ', U1.LAST_NAME)
 ,[WHO_CREATED_USERNAME] = U1.USER_NAME
 ,[WHO_MODIFIED] = [C].[WHO_MODIFIED]
 ,[WHO_MODIFIED_NAME] = Concat(U4.FIRST_NAME, ' ', U4.LAST_NAME)
 ,[WHO_MODIFIED_USERNAME] = U4.USER_NAME
 ,[WHEN_MODIFIED] = [C].[WHEN_MODIFIED]
 ,[DUE_DATE] = [C].[DUE_DATE]
 ,[OWNER] = [C].[OWNER]
 ,[OWNER_NAME] = Concat(U3.FIRST_NAME, ' ', U3.LAST_NAME)
 ,[OWNER_USERNAME] = U3.USER_NAME
 ,[CUSTOMER_ID] = [C].[CUSTOMER_ID]
 ,[SEVERITY] = [C].[SEVERITY]
 ,[SUBJECT] = [C].[SUBJECT]
 ,[CASE_ACCESS] = [C].[CASE_ACCESS]
 ,[DESCRIPTION] = [C].[DESCRIPTION]
 ,[SOLUTION_DESCRIPTION] = [C].[SOLUTION_DESCRIPTION]
 ,[FOLDER_ID] = [C].[FOLDER_ID]
 ,[USER_LAST_WORKED] = [C].[USER_LAST_WORKED]
 ,[USER_LAST_NAME] = Concat(U2.FIRST_NAME, ' ', U2.LAST_NAME)
 ,[USER_LAST_USERNAME] = U2.USER_NAME
 ,[DELETE_FLAG] = [C].[DELETE_FLAG]
 ,[CREATE_DATE] = [C].[CREATE_DATE]
 
 FROM [eGReportsDB].[dbo].[EGPL_CASEMGMT_CASE] [C]
  LEFT JOIN EGPL_DEPARTMENT D ON C.DEPARTMENT_ID = D.DEPARTMENT_ID
  LEFT JOIN EGPL_USER U1 ON U1.USER_ID = C.WHO_CREATED
  LEFT JOIN EGPL_USER U2 ON U2.USER_ID = C.USER_LAST_WORKED
  LEFT JOIN EGPL_USER U3 ON U3.USER_ID = C.OWNER
  LEFT JOIN EGPL_USER U4 ON U4.USER_ID = C.WHO_MODIFIED

TEMP 1

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT 
       [Agent] = Concat(U2.FIRST_NAME, ' ', U2.LAST_NAME)
	  ,[Activity Subtype] =  CASE(A.ACTIVITY_SUB_TYPE)
	  WHEN '1' THEN 'General'
	  WHEN '2' THEN 'Webform'
	  WHEN '3' THEN 'Secure'
	  WHEN '4' THEN 'Permanent undeliverable'
	  WHEN '5' THEN 'Temporary undeliverable'
	  WHEN '6' THEN 'Reply'
	  WHEN '7' THEN 'Forwarded'
	  WHEN '8' THEN 'Composed'
	  WHEN '9' THEN 'Auto reply'
	  WHEN '10' THEN 'Auto acknoledgement'
	  WHEN '11' THEN 'Group reply'
	  WHEN '12' THEN 'Redirected'
	  WHEN '13' THEN 'Undispatchable'
	  WHEN '14' THEN 'Suervised and accepted'
	  WHEN '15' THEN 'Supervised and rejected'
	  WHEN '16' THEN 'Supervisoed and resubmitted'
	  WHEN '2001' THEN 'Chat - General'
	  WHEN '2002' THEN 'Chat - Video chat'
	  WHEN '8001' THEN 'Web - General'
	  ELSE CAST(ACTIVITY_SUB_TYPE AS VARCHAR(50))
	  END
	  --,[WHEN_CREATED] = [A].[WHEN_CREATED]
	  --,[WHEN_MODIFIED] = [A].[WHEN_MODIFIED]
	  ,[DATE] = CONVERT(Datetime,(CONVERT(CHAR(10),[A].[WHEN_MODIFIED],102)),102)
	  --,[Date] = (CONVERT(CHAR(10),[A].[WHEN_MODIFIED],103))
      ,[Agent Name] = U2.USER_NAME
	  --,[CREATE_DATE] = [A].[CREATE_DATE]
      ,[Alias] = A.alias
	  ,[Department Name] = D.[DEPARTMENT_NAME]
	  ,[Queue Name] = Q.QUEUE_NAME
	  ,Incoming='0'
	  ,[Incoming (Existing)]='0'
	  ,[Manual Replies]='0'
	  --,[Completed]='1'
	  ,COUNT(*) AS 'Completed'
	  ,[Composed]='0'
	  --,[Actvity Duration]='0'
	  --,[Actvity Duration]=([A].[WHEN_MODIFIED]-[A].[WHEN_CREATED])
	  ,[Avg FIRST Manual Reply TIME] = '00:00:00'
  FROM 
  [EGPL_CASEMGMT_ACTIVITY] A
  LEFT JOIN EGPL_DEPARTMENT D ON A.DEPARTMENT_ID = D.DEPARTMENT_ID
  LEFT JOIN EGPL_USER U2 ON U2.USER_ID = A.ASSIGNED_TO
  LEFT JOIN EGPL_ROUTING_QUEUE Q ON Q.QUEUE_ID = A.QUEUE_ID
 
WHERE A.ACTIVITY_STATUS = '9000' 
AND A.ACTIVITY_SUB_TYPE = '1'
 
GROUP BY Concat(U2.FIRST_NAME, ' ', U2.LAST_NAME),A.ACTIVITY_SUB_TYPE,U2.USER_NAME, A.alias, D.[DEPARTMENT_NAME],Q.QUEUE_NAME,CONVERT(Datetime,(CONVERT(CHAR(10),[A].[WHEN_MODIFIED],102)),102)
 
ORDER BY CONVERT(Datetime,(CONVERT(CHAR(10),[A].[WHEN_MODIFIED],102)),102),Concat(U2.FIRST_NAME, ' ', U2.LAST_NAME),alias

Install Info

SELECT * FROM EGPL_RELEASE_INFO ORDER BY INSTALL_DATE DESC

Chat SQL

Number of chats sent / receipts per chat conversation (activityId)

Each Activity ID

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

If you want to get the Median / Half way point - you can use SQL TOP 50 Percent 

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

Average Count for all Activity ID (Grouped by EntryPoint Id)

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

SQL User <> CCE Mapping

SELECT *
FROM EGICM_USER
    LEFT JOIN EGPL_USER ON EGPL_USER.user_id = EGICM_USER.user_id
 
ORDER BY FIRST_NAME, LAST_NAME