Custom Call Type - Bucket Reports

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 NetQTime >=0 AND NetQTime <= 20  THEN 1
WHEN NetQTime > 20 AND NetQTime <= 60 THEN 2
WHEN NetQTime > 60 AND NetQTime <= 180 THEN 3
WHEN NetQTime > 180 AND NetQTime <= 300 THEN 4
WHEN NetQTime > 300 AND NetQTime <= 600 THEN 5
WHEN NetQTime > 600 AND NetQTime <= 900 THEN 6
WHEN NetQTime > 900 AND NetQTime <= 1200 THEN 7
WHEN NetQTime > 1200 AND NetQTime <= 1500 THEN 8
WHEN NetQTime > 1500 AND NetQTime <= 1800 THEN 9
WHEN NetQTime > 1800 AND NetQTime <= 2700 THEN 10
WHEN NetQTime > 2700 AND NetQTime <= 3600 THEN 11
WHEN NetQTime > 3600 THEN 12
ELSE 0 
END AS BucketInterval
 
FROM 
Termination_Call_Detail TCD (nolock)
 
WHERE 
 
  TCD.DateTime >= '2020-01-01 00:00:00'
  AND TCD.DateTime < '2020-10-02 23:59:59'
  AND TCD.PeripheralID IN (5000) 
  AND TCD.CallDispositionFlag = '1'
)
 
,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

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