Hi
I had to rewrite a query to list missed and answered calls.
The reason is that the original has the wrong start and end dates. It appears that an agent has answered two calls at the same time, which is impractical in the real configuration.
The problem I'm facing is that the new query, which has correct dates, is not showing around 5% of the links presented by the original query.
Any help or idea is important.
Original Query:
;WITH DetalheChamada AS
(
SELECT
Cast(CONCAT(tcd.RouterCallKeyDay,tcd.RouterCallKey) AS decimal) AS 'Identificador',
tcd.RouterCallKeySequenceNumber,
MIN(FORMAT(dbo.Route_Call_Detail.BeganCallTypeDateTime,'dd/MM/yyyy HH:mm:ss')) [Data Inicio],
MAX(FORMAT(DATEADD(SECOND, tcd.Duration, dbo.Route_Call_Detail.BeganCallTypeDateTime),'dd/MM/yyyy HH:mm:ss')) [Data Fim],
p.LastName AS Nome_Agente,
p.LoginName,
dbo.Agent.PeripheralNumber AS Agent_ID,
tcd.InstrumentPortNumber AS Ramal,
tcd.Duration,
TempoFila = IIF(tcd.NetQTime = 0, tcd.LocalQTime, tcd.NetQTime),
tcd.RingTime,
tcd.WorkTime,
tcd.HoldTime,
tcd.TalkTime,
tcd.DelayTime,
tcd.NetworkTime,
tcd.ANI,
tcd.TimeToAband,
tcd.DNIS,
tcd.DigitsDialed AS NR_Entrada,
dbo.Precision_Queue.EnterpriseName AS Queue_Name,
tcd.PrecisionQueueID ,
dbo.Call_Type.EnterpriseName AS Calltype_Name,
TCD.CallTypeID,
tcd.CallDisposition,
dbo.Route_Call_Detail.Variable1 ,
dbo.Route_Call_Detail.Variable2 ,
dbo.Route_Call_Detail.Variable3 ,
dbo.Route_Call_Detail.Variable4 ,
dbo.Route_Call_Detail.Variable5 ,
dbo.Route_Call_Detail.Variable6 ,
dbo.Route_Call_Detail.Variable7 ,
dbo.Route_Call_Detail.Variable8 ,
dbo.Route_Call_Detail.Variable9 ,
dbo.Route_Call_Detail.Variable10 ,
Finalization =
CASE
WHEN tcd.CallDisposition=14 THEN ('Saida')
WHEN tcd.CallDisposition in (28,29) THEN ('Atend/Transf')
WHEN tcd.CallDisposition in (3,6) THEN ('Aband Ring')
WHEN tcd.CallDisposition in (1,2,4,5) THEN ('Aband Fila')
WHEN (tcd.AgentSkillTargetID IS NOT NULL and tcd.CallDisposition in (13)) THEN ('Atendidas')
ELSE ''
END
FROM
dbo.Termination_Call_Detail tcd
INNER JOIN
dbo.Termination_Call_Detail TCD on tcd.RecoveryKey = TCD.RecoveryKey
LEFT JOIN
dbo.Skill_Group ON tcd.SkillGroupSkillTargetID = dbo.Skill_Group.SkillTargetID
LEFT JOIN
dbo.Call_Type ON tcd.CallTypeID = dbo.Call_Type.CallTypeID
LEFT JOIN
dbo.Precision_Queue ON tcd.PrecisionQueueID = dbo.Precision_Queue.PrecisionQueueID
INNER JOIN
dbo.Route_Call_Detail ON tcd.RouterCallKey = dbo.Route_Call_Detail.RouterCallKey AND tcd.RouterCallKeyDay = dbo.Route_Call_Detail.RouterCallKeyDay
LEFT JOIN
dbo.Agent ON tcd.AgentSkillTargetID = dbo.Agent.SkillTargetID
LEFT JOIN
dbo.Person p on Agent.PersonID = p.PersonID
WHERE
tcd.RouterCallKeyDay <> 0
AND
Route_Call_Detail.BeganCallTypeDateTime BETWEEN '2021-06-11 00:00:00' and '2021-06-11 23:58:59'
GROUP BY
Cast(CONCAT(tcd.RouterCallKeyDay,tcd.RouterCallKey) AS decimal),
tcd.RouterCallKeySequenceNumber,
p.LastName,
p.LoginName,
dbo.Agent.PeripheralNumber,
tcd.InstrumentPortNumber,
tcd.Duration,
IIF(tcd.NetQTime = 0, tcd.LocalQTime, tcd.NetQTime),
tcd.RingTime,
tcd.WorkTime,
tcd.HoldTime,
tcd.TalkTime,
tcd.DelayTime,
tcd.NetworkTime,
tcd.ANI,
tcd.TimeToAband,
tcd.DNIS,
tcd.DigitsDialed,
dbo.Precision_Queue.EnterpriseName,
tcd.PrecisionQueueID ,
dbo.Call_Type.EnterpriseName,
TCD.CallTypeID,
tcd.CallDisposition,
dbo.Route_Call_Detail.Variable1 ,
dbo.Route_Call_Detail.Variable2 ,
dbo.Route_Call_Detail.Variable3 ,
dbo.Route_Call_Detail.Variable4 ,
dbo.Route_Call_Detail.Variable5 ,
dbo.Route_Call_Detail.Variable6 ,
dbo.Route_Call_Detail.Variable7 ,
dbo.Route_Call_Detail.Variable8 ,
dbo.Route_Call_Detail.Variable9 ,
dbo.Route_Call_Detail.Variable10 ,
CASE
WHEN tcd.CallDisposition=14 THEN ('Saida')
WHEN tcd.CallDisposition in (28,29) THEN ('Atend/Transf')
WHEN tcd.CallDisposition in (3,6) THEN ('Aband Ring')
WHEN tcd.CallDisposition in (1,2,4,5) THEN ('Aband Fila')
WHEN (tcd.AgentSkillTargetID IS NOT NULL and tcd.CallDisposition in (13)) THEN ('Atendidas')
ELSE ''
END
),
LigacaoAtendida AS
(
SELECT
*
FROM
DetalheChamada D
WHERE
D.PrecisionQueueID IN (
SELECT DISTINCT
F.PrecisionQueueID
FROM Termination_Call_Detail F
WHERE F.CallTypeID IN (5154) --(5144)
AND F.DateTime BETWEEN '2021-06-11 00:00:00' and '2021-06-11 23:58:59'
)
),
LigacaoAbandonada AS
(
SELECT
*
FROM
DetalheChamada D
WHERE D.CallTypeID IN (5154) --(5144)
AND
D.TimeToAband > 0
)
SELECT * FROM LigacaoAtendida A
WHERE DNIS not like '77777%' AND CallDisposition NOT in (1,2)
UNION ALL
SELECT * FROM LigacaoAbandonada B
WHERE DNIS is not null;
NEW query I rewrote:
-- Variáveis para Relatório
--
DECLARE @CallTypeID as INTEGER;
DECLARE @start_date as datetime;
DECLARE @end_date as datetime;
SET @CallTypeID = 5144
SET @start_date = '2021-06-11 00:00:00'
SET @end_date = '2021-06-11 23:58:59'
-- Query Principal
--
SELECT DISTINCT
Cast(CONCAT(tcd.RouterCallKeyDay,tcd.RouterCallKey) AS decimal) AS 'Identificador',
tcd.RouterCallKeySequenceNumber,
FORMAT(DATEADD(MINUTE, -tcd.[TimeZone], tcd.[StartDateTimeUTC]),'dd/MM/yyyy HH:mm:ss') [Data Inicio],
FORMAT(DATEADD(SECOND, tcd.Duration, DATEADD(MINUTE, -tcd.[TimeZone], tcd.[StartDateTimeUTC])),'dd/MM/yyyy HH:mm:ss') [Data Fim],
p.LastName AS Nome_Agente,
p.LoginName,
a.PeripheralNumber AS Agent_ID,
tcd.InstrumentPortNumber AS Ramal,
tcd.Duration,
TempoFila = IIF(tcd.NetQTime = 0, tcd.LocalQTime, tcd.NetQTime),
tcd.RingTime,
tcd.WorkTime,
tcd.HoldTime,
tcd.TalkTime,
tcd.DelayTime,
tcd.NetworkTime,
tcd.ANI,
tcd.TimeToAband,
tcd.DNIS,
tcd.DigitsDialed AS NR_Entrada,
pq.EnterpriseName AS Queue_Name,
tcd.PrecisionQueueID ,
ct.EnterpriseName AS Calltype_Name,
tcd.CallTypeID,
tcd.CallDisposition,
tcd.Variable1 ,
tcd.Variable2 ,
tcd.Variable3 ,
tcd.Variable4 ,
tcd.Variable5 ,
tcd.Variable6 ,
tcd.Variable7 ,
tcd.Variable8 ,
tcd.Variable9 ,
tcd.Variable10 ,
Finalization =
CASE
WHEN tcd.CallDisposition in (1,2,4,5) THEN ('Aband Fila')
WHEN tcd.CallDisposition in (3,6) THEN ('Aband Ring')
WHEN (tcd.AgentSkillTargetID IS NOT NULL and tcd.CallDisposition in (13)) THEN ('Atendidas')
WHEN tcd.CallDisposition=14 THEN ('Saida')
WHEN tcd.CallDisposition in (28,29) THEN ('Atend/Transf')
WHEN tcd.CallDisposition in (30) THEN ('Confer')
ELSE ''
END
FROM
dbo.Termination_Call_Detail tcd
LEFT JOIN
dbo.Skill_Group sg ON tcd.SkillGroupSkillTargetID = sg.SkillTargetID
LEFT JOIN
dbo.Call_Type ct ON tcd.CallTypeID = ct.CallTypeID
LEFT JOIN
dbo.Precision_Queue pq ON tcd.PrecisionQueueID = pq.PrecisionQueueID
INNER JOIN
dbo.Route_Call_Detail rcd ON tcd.RouterCallKey = rcd.RouterCallKey AND tcd.RouterCallKeyDay = rcd.RouterCallKeyDay
LEFT JOIN
dbo.Agent a ON tcd.AgentSkillTargetID = a.SkillTargetID
LEFT JOIN
dbo.Person p on a.PersonID = p.PersonID
WHERE
tcd.RouterCallKeyDay > 0
AND tcd.[DateTime] BETWEEN @start_date AND @end_date -- tempo da TCD ?
AND DATEADD(MINUTE, -tcd.[TimeZone], tcd.[StartDateTimeUTC]) BETWEEN @start_date AND @end_date -- tempo do início
AND rcd.BeganCallTypeDateTime BETWEEN @start_date AND @end_date -- tempo da RCD ?
-- AND tcd.[DateTime] BETWEEN DATEADD(MINUTE, -60,@start_date) AND DATEADD(MINUTE, 60,@end_date) -- teste tempo da TCD ?
-- AND rcd.DateTime BETWEEN @start_date AND @end_date -- '2021-06-11 10:00:00' and '2021-06-11 10:14:59'
AND tcd.PrecisionQueueID IN (
SELECT DISTINCT
F.PrecisionQueueID
FROM Termination_Call_Detail F
WHERE F.CallTypeID IN (@CallTypeID)
AND F.DateTime BETWEEN @start_date AND @end_date --'2021-06-11 00:00:00' and '2021-06-11 23:58:59'
)
AND tcd.CallTypeID IN (@CallTypeID)
-- AND tcd.[Duration] > 0
-- AND tcd.CallDisposition IN (1,2,4,5,3,6,13,14,28,29)
-- /*
AND -- Separação das Ligações Atendidas e Abandonadas
(
-- Abandonadas
(DNIS is not null
AND tcd.CallDisposition IN (1,2,4,5,3,6)
AND tcd.TimeToAband > 0
)
OR
-- Atendidas
(DNIS not like '77777%'
AND tcd.CallDisposition IN (13,14,28,29,30)
-- AND tcd.CallDisposition NOT in (1,2)
-- AND tcd.TimeToAband = 0
)
)
-- */
