Differences

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

--- vendors:cisco:uc:icm:sql:tcd-tcv [2022/12/13 07:56] – gerardorourke
+++ vendors:cisco:uc:icm:sql:tcd-tcv [2024/04/12 18:10] (current) – [Call Type with Average Queue Time (from TCD)] gerardorourke
@@ Line 1: / Line 1: @@
-====== TCD SQL Query ======
+====== TCD SQL ======
+===== TCD Example =====
 <code sql>
-SELECT TOP (8001)
+ SELECT TOP (8001)
  TCDRecoveryKey = TCD.RecoveryKey
+,PeripheralName = Peripheral.EnterpriseName
 ,RecordStartDateTimeUTC = TCD.StartDateTimeUTC
 ,DbDateTime=TCD.DbDateTime
@@ Line 215: / Line 217: @@
  WHERE
-  DATEADD(MINUTE,-TCD.TimeZone,TCD.StartDateTimeUTC) >=  :start_date
+(TCD.DateTime >= '2022-12-13 00:00:00' AND TCD.DateTime <= '2022-12-13 23:59:59')
-  AND DATEADD(MINUTE,-TCD.TimeZone,TCD.StartDateTimeUTC) < :end_date
+  -- More useful Filters commented out below in "CUIC" format
-  AND TCD.PeripheralID IN (5000)
+  --DATEADD(MINUTE,-TCD.TimeZone,TCD.StartDateTimeUTC) >=  :start_date
-  AND TCD.AgentSkillTargetID IN (:AgentID)
+  --AND DATEADD(MINUTE,-TCD.TimeZone,TCD.StartDateTimeUTC) < :end_date
-  AND TCD.ANI LIKE (:ANI)
+  --AND TCD.PeripheralID IN (5000)
-  AND TCD.DigitsDialed LIKE (:DigitsDialed)
+  --AND TCD.AgentSkillTargetID IN (:AgentID)
+  --AND TCD.ANI LIKE (:ANI)
+  --AND TCD.DigitsDialed LIKE (:DigitsDialed)
  ORDER BY
 StartDateTimeUTC , RouterCallKeyDay,   RouterCallKey,   RouterCallKeySequenceNumber
 </code>
 ====== TCD with TCV Pivot ======
@@ Line 775: / Line 777: @@
 WHERE
-(TCD.DateTime >= '2022-12-13 00:00:00' AND TCD.DateTime <= '2022-12-13 23:59:59')
+(TCD.DateTime >= '2019-07-11 00:00:00' AND TCD.DateTime <= '2019-07-11 23:59:59')
-ORDER BY TCD.DateTime
+--(TCD.DateTime >= '2018-07-23 00:00:00' AND TCD.DateTime <= '2018-07-23 23:59:59')
+--AND CallDispositionFlag = '2'
+--GROUP BY CONVERT(Datetime,(CONVERT(char(10),[DateTime],102)),102), TCD.CallDispositionFlag, TCD.CallTypeID, Call_Type.EnterpriseName
+GROUP BY TCD.CallDispositionFlag, Call_Type.EnterpriseName
+--ORDER BY CONVERT(Datetime,(CONVERT(char(10),[DateTime],102)),102), Call_Type.EnterpriseName
+--ORDER BY Call_Type.EnterpriseName
+ORDER BY
+--Count DESC
+Call_Type.EnterpriseName
+</code>
+===== TCD - Number of Calls for a specific interval and Total Duration of calls in seconds and also in minutes =====
+<code sql>
+SELECT
+ NumOfCalls = Count(*)
+,DurationSec = SUM(Duration)
+,DurationMin = SUM(Duration)/60
+FROM Termination_Call_Detail TCD
+WHERE
+(TCD.DateTime >= '2024-01-01 00:00:00' AND TCD.DateTime <= '2024-01-31 23:59:59')
+AND TCD.PeripheralCallType = '42'
+AND TCD.DigitsDialed = '15551234'
 </code>