Differences

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

--- vendors:cisco:uc:icm:sql:bucket [2020/10/02 08:05] – created gerardorourke
+++ vendors:cisco:uc:icm:sql:bucket [2020/10/02 08:12] (current) – [Call Type Abandoned in IVR Custom Bucket Interval Report] gerardorourke
@@ Line 1: / Line 1: @@
 ====== Custom Call Type - Bucket Reports ======
+===== Call Type Answered Custom Bucket Interval Report =====
 <code sql>
-        SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
+SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
@@ Line 50: / Line 52: @@
 FROM TEMP_TABLE2 T2
-JOIN Call_Type (nolock) on (T2.CallTypeID = Call_Type.CallTypeID)
 ) AS T PIVOT (MAX(BucketCount) FOR BucketInterval IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PIVOT_TABLE
 )
@@ Line 75: / Line 76: @@
 ORDER BY Call_Type.EnterpriseName
-</sql>
+</code>
+===== Call Type Abandoned in IVR Custom Bucket Interval Report =====
+<code sql>
+SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
+;WITH TEMP_TABLE1 AS (
+SELECT
+CallTypeID=TCD.CallTypeID
+,CASE
+WHEN LocalQTime >=0 AND LocalQTime <= 20  THEN 1
+WHEN LocalQTime > 20 AND LocalQTime <= 60 THEN 2
+WHEN LocalQTime > 60 AND LocalQTime <= 180 THEN 3
+WHEN LocalQTime > 180 AND LocalQTime <= 300 THEN 4
+WHEN LocalQTime > 300 AND LocalQTime <= 600 THEN 5
+WHEN LocalQTime > 600 AND LocalQTime <= 900 THEN 6
+WHEN LocalQTime > 900 AND LocalQTime <= 1200 THEN 7
+WHEN LocalQTime > 1200 AND LocalQTime <= 1500 THEN 8
+WHEN LocalQTime > 1500 AND LocalQTime <= 1800 THEN 9
+WHEN LocalQTime > 1800 AND LocalQTime <= 2700 THEN 10
+WHEN LocalQTime > 2700 AND LocalQTime <= 3600 THEN 11
+WHEN LocalQTime > 3600 THEN 12
+ELSE 0
+END AS BucketInterval
+FROM
+Termination_Call_Detail TCD (nolock)
+WHERE
+  TCD.DateTime >= '2020-09-01 00:00:00'
+  AND TCD.DateTime < '2020-09-30 23:59:59'
+  AND TCD.PeripheralID IN (5002,5003)
+  AND TCD.CallDispositionFlag = '2'
+)
+,TEMP_TABLE2 AS (SELECT
+ CallTypeID = CallTypeID
+,BucketInterval = BucketInterval
+,BucketCount =  COUNT(*) from TEMP_TABLE1
+GROUP BY
+ CallTypeID
+,BucketInterval
+)
+,TEMP_PIVOT AS (SELECT * From (SELECT
+ CallTypeID = T2.CallTypeID
+,BucketInterval = BucketInterval
+,BucketCount
+FROM TEMP_TABLE2 T2
+) AS T PIVOT (MAX(BucketCount) FOR BucketInterval IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS PIVOT_TABLE
+)
+SELECT
+ CallTypeID = TP.CallTypeID
+,CalltypeName = Call_Type.EnterpriseName
+,Interval_01 = isNull(TP.[1],0)
+,Interval_02 = isNull(TP.[2],0)
+,Interval_03 = isNull(TP.[3],0)
+,Interval_04 = isNull(TP.[4],0)
+,Interval_05 = isNull(TP.[5],0)
+,Interval_06 = isNull(TP.[6],0)
+,Interval_07 = isNull(TP.[7],0)
+,Interval_08 = isNull(TP.[8],0)
+,Interval_09 = isNull(TP.[9],0)
+,Interval_10 = isNull(TP.[10],0)
+,Interval_11 = isNull(TP.[11],0)
+,Interval_12 = isNull(TP.[12],0)
+FROM TEMP_PIVOT TP
+JOIN Call_Type (nolock) on (TP.CallTypeID = Call_Type.CallTypeID)
+ORDER BY Call_Type.EnterpriseName
+</code>