Reportes: Estados de los agentes

De Vocalcom.latam Wiki
Saltar a: navegación, buscar
SELECT CONVERT(CHAR(10), ODA.ActionLocalTime, 103) AS Fecha
	,CONVERT(CHAR(4), ODA.OriginatorID) + ' - ' + LA.Nom + CASE 
		WHEN LA.Prenom <> ''
			THEN ', ' + LA.Prenom
		ELSE ''
		END AS Agente
	,CASE 
		WHEN ODA.STATE BETWEEN 1
				AND 300
			THEN REPLACE(S.Description, 'Agent-', '')
		ELSE PC.Description
		END AS Estado
	,COUNT(*) AS Cantidad
	,CONVERT(CHAR(8), DATEADD(MS, SUM(Duration) * 10, 0), 114) AS Tiempo
FROM ODActions AS ODA
LEFT JOIN States AS S ON ODA.STATE = S.StateID
LEFT JOIN HN_Admin.dbo.PauseCodes AS PC ON ODA.STATE - 300 = PC.PauseCode
LEFT JOIN HN_Admin.dbo.ListAgents AS LA ON ODA.OriginatorID = LA.Ident
WHERE ODA.CustomerID = 1
	AND ODA.ActionLocalTime BETWEEN '20150527'
		AND '20150528'
	AND ODA.OriginatorID IS NOT NULL
	AND ODA.OriginatorID <> ''
	AND ODA.OriginatorID <> '0'
	AND ODA.STATE BETWEEN 1
		AND 399
GROUP BY CONVERT(CHAR(10), ODA.ActionLocalTime, 103)
	,CONVERT(CHAR(4), ODA.OriginatorID) + ' - ' + LA.Nom + CASE 
		WHEN LA.Prenom <> ''
			THEN ', ' + LA.Prenom
		ELSE ''
		END
	,CASE 
		WHEN ODA.STATE BETWEEN 1
				AND 300
			THEN REPLACE(S.Description, 'Agent-', '')
		ELSE PC.Description
		END
ORDER BY CONVERT(CHAR(10), ODA.ActionLocalTime, 103)
	,CONVERT(CHAR(4), ODA.OriginatorID) + ' - ' + LA.Nom + CASE 
		WHEN LA.Prenom <> ''
			THEN ', ' + LA.Prenom
		ELSE ''
		END
	,CASE 
		WHEN ODA.STATE BETWEEN 1
				AND 300
			THEN REPLACE(S.Description, 'Agent-', '')
		ELSE PC.Description
		END


Fecha Agente Estado Cantidad Tiempo
27/05/2015 1003 - Morad, Jaime After call 199 01:16:16
27/05/2015 1003 - Morad, Jaime Alerting 209 00:42:44
27/05/2015 1003 - Morad, Jaime Almuerzo 1 01:02:27
27/05/2015 1003 - Morad, Jaime Baño 1 00:02:16
27/05/2015 1003 - Morad, Jaime Break mañana 1 00:15:52
27/05/2015 1003 - Morad, Jaime Break Tarde 1 00:16:45
27/05/2015 1003 - Morad, Jaime Dialing 14 00:06:23
27/05/2015 1003 - Morad, Jaime Online 81 02:03:53
27/05/2015 1003 - Morad, Jaime Previewing 38 00:17:27
27/05/2015 1003 - Morad, Jaime Ready 19 02:35:28
27/05/2015 1003 - Morad, Jaime Ready blended 44 00:02:12
27/05/2015 1003 - Morad, Jaime Retroalimentacion 2 00:38:47
27/05/2015 1003 - Morad, Jaime System pause 3 00:00:01
27/05/2015 1003 - Morad, Jaime TemporaryNotReady 1 00:00:01
27/05/2015 1026 - Malaver Peña, Juan Manuel Alerting 1 00:20:43
27/05/2015 1026 - Malaver Peña, Juan Manuel Supervising 1 00:00:06
27/05/2015 1041 - Hurtado, Maria  After call 53 01:01:27
27/05/2015 1041 - Hurtado, Maria  Alerting 58 00:09:23
27/05/2015 1041 - Hurtado, Maria  Almuerzo 1 00:59:54
27/05/2015 1041 - Hurtado, Maria  Atención personalizada 1 01:13:39
27/05/2015 1041 - Hurtado, Maria  Break mañana 1 00:14:48
27/05/2015 1041 - Hurtado, Maria  Break Tarde 1 00:14:40
27/05/2015 1041 - Hurtado, Maria  Calling back 5 00:02:02
27/05/2015 1041 - Hurtado, Maria  Dialing 12 00:07:01
27/05/2015 1041 - Hurtado, Maria  Online 31 01:28:48
27/05/2015 1041 - Hurtado, Maria  Previewing 8 00:11:38
27/05/2015 1041 - Hurtado, Maria  Ready 24 02:19:26
27/05/2015 1041 - Hurtado, Maria  Ready blended 11 00:00:20
27/05/2015 1041 - Hurtado, Maria  Retroalimentacion 1 00:38:45
27/05/2015 1041 - Hurtado, Maria  System pause 1 00:00:00