Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @CUSTOMERID INT = 1
- DECLARE @RANDOM VARCHAR(12) = CAST(CAST(RAND() * 1000000000 AS INT) AS VARCHAR)
- DECLARE @QUERY NVARCHAR(MAX)
- DECLARE @QUERY_LIST TABLE
- (
- ReportType VARCHAR(50),
- TableName VARCHAR(200)
- )
- INSERT INTO @QUERY_LIST (ReportType, TableName)
- SELECT ReportType, REPLACE(REPLACE(ViewName, '[STAT_SERVER_LAM075\SQLEXPERTAT_SQLEXPERTAT_VONDATHT].[VONDATHT].DBO.VWINBOUNDCALLS', '[VONDATHT]..ODCalls'), 'VWINBOUNDCALLS', 'ODCalls')
- FROM vreportt.dbo.QueryList
- WHERE CustomerID = @CUSTOMERID
- AND MinTime <= CONVERT(CHAR(8), @FECHAHASTA, 112) + '000000'
- AND MaxTime >= CONVERT(CHAR(8), @FECHADESDE, 112) + '000000'
- AND ReportType = 'INBOUND'
- SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
- EXEC sp_executesql @QUERY
- SET @QUERY = 'CREATE TABLE ##Encuesta_{RANDOM} (CALL_ID char(32),
- RTA_2 INT,
- RTA_3 INT,
- RTA_4 INT,
- RTA_5 INT,
- RTA_6 INT,
- )
- INSERT INTO ##Encuesta_{RANDOM}
- SELECT CALL_ID,RTA_2,RTA_3,RTA_4,RTA_5,RTA_6 FROM [vcampat]..[Encuestas_Entrantes] AS ENC'
- SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
- EXEC sp_executesql @QUERY
- SET @QUERY = 'CREATE TABLE ##ODCalls_{RANDOM} (
- ID CHAR(32),
- CustomerID INT,
- Indice NUMERIC(18),
- CallType INT,
- CallUniversalTime DATETIME,
- CallLocalTime DATETIME,
- CallUniversalTimeString VARCHAR(14),
- CallLocalTimeString VARCHAR(14),
- Duration INT DEFAULT 0,
- CallDuration INT DEFAULT 0,
- AcceptDuration INT DEFAULT 0,
- IvrDuration INT DEFAULT 0,
- WaitDuration INT DEFAULT 0,
- TotalWaitDuration INT DEFAULT 0,
- ConvDuration INT DEFAULT 0,
- WrapupDuration INT DEFAULT 0,
- RerouteDuration INT DEFAULT 0,
- OverflowDuration INT DEFAULT 0,
- ANI VARCHAR(60),
- DNIS VARCHAR(60),
- FirstCampaign VARCHAR(255),
- FirstVirtualCamp VARCHAR(255),
- LastCampaign VARCHAR(255),
- LastVirtualCamp VARCHAR(255),
- UUI VARCHAR(255),
- Memo NVARCHAR(255),
- AssociatedData NVARCHAR(255),
- OutTel VARCHAR(60),
- OutDialed VARCHAR(100),
- Closed INT,
- NoAgent INT,
- Overflow INT,
- Abandon INT,
- FirstIVR VARCHAR(255),
- LastIVR VARCHAR(255),
- FirstQueue INT,
- LastQueue INT,
- InitPriority INT,
- FirstAgent INT,
- LastAgent INT,
- LastTransfer VARCHAR(60),
- CallStatusGroup INT,
- CallStatusNum INT,
- CallStatusDetail INT,
- Comments NVARCHAR(255),
- ContactID VARCHAR(50),
- EndByAgent INT,
- AgentListen INT,
- EndReason INT,
- RefID VARCHAR(32),
- ProActiveReason NVARCHAR(100)
- )'
- SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
- EXEC sp_executesql @QUERY
- DECLARE @TABLENAME VARCHAR(200)
- DECLARE @REPORTTYPE VARCHAR(50)
- DECLARE CURSOR_TABLAS CURSOR
- FOR
- SELECT *
- FROM @QUERY_LIST
- OPEN CURSOR_TABLAS
- FETCH NEXT FROM CURSOR_TABLAS
- INTO @REPORTTYPE, @TABLENAME
- WHILE @@FETCH_STATUS = 0
- BEGIN
- IF @REPORTTYPE = 'INBOUND' -- INSERTAMOS EN LA TABLA TEMPORAL DE ODCALLS
- BEGIN
- SET @QUERY = 'INSERT INTO ##ODCalls_{RANDOM} (ID,' +
- 'Indice,' +
- 'CallType,' +
- 'CallLocalTime,' +
- 'Duration,' +
- 'CallDuration,' +
- 'AcceptDuration,' +
- 'IvrDuration,' +
- 'WaitDuration,' +
- 'TotalWaitDuration,' +
- 'ConvDuration,' +
- 'WrapupDuration,' +
- 'ANI,' +
- 'DNIS,' +
- 'FirstCampaign,' +
- 'FirstVirtualCamp,' +
- 'LastCampaign,' +
- 'LastVirtualCamp,' +
- 'UUI,' +
- 'Memo,' +
- 'AssociatedData,' +
- 'OutTel,' +
- 'OutDialed,' +
- 'Closed,' +
- 'NoAgent,' +
- 'Overflow,' +
- 'Abandon,' +
- 'FirstIVR,' +
- 'LastIVR,' +
- 'FirstQueue,' +
- 'LastQueue,' +
- 'InitPriority,' +
- 'FirstAgent,' +
- 'LastAgent,' +
- 'LastTransfer,' +
- 'CallStatusGroup,' +
- 'CallStatusNum,' +
- 'CallStatusDetail,' +
- 'Comments,' +
- 'ContactID,' +
- 'EndByAgent,' +
- 'AgentListen,' +
- 'EndReason,' +
- 'RefID,' +
- 'ProActiveReason) ' +
- --carga de datos en tabla temporal--
- 'SELECT ID,' +
- 'Indice,' +
- 'CallType,' +
- 'CallLocalTime,' +
- 'Duration,' +
- 'CallDuration,' +
- 'AcceptDuration,' +
- 'IvrDuration,' +
- 'WaitDuration,' +
- 'TotalWaitDuration,' +
- 'ConvDuration,' +
- 'WrapupDuration,' +
- 'ANI,' +
- 'DNIS,' +
- 'FirstCampaign,' +
- 'FirstVirtualCamp,' +
- 'LastCampaign,' +
- 'LastVirtualCamp,' +
- 'UUI,' +
- 'Memo,' +
- 'AssociatedData,' +
- 'OutTel,' +
- 'OutDialed,' +
- 'Closed,' +
- 'NoAgent,' +
- 'Overflow,' +
- 'Abandon,' +
- 'FirstIVR,' +
- 'LastIVR,' +
- 'FirstQueue,' +
- 'LastQueue,' +
- 'InitPriority,' +
- 'FirstAgent,' +
- 'LastAgent,' +
- 'LastTransfer,' +
- 'CallStatusGroup,' +
- 'CallStatusNum,' +
- 'CallStatusDetail,' +
- 'Comments,' +
- 'ContactID,' +
- 'EndByAgent,' +
- 'AgentListen,' +
- 'EndReason,' +
- 'RefID,' +
- 'ProActiveReason ' +
- 'FROM {TABLENAME} ' +
- 'WHERE CustomerID = @CUSTOMERID ' +
- 'AND CallLocalTime <= @FECHAHASTA ' +
- 'AND CallLocalTime >= @FECHADESDE ' +
- 'AND ANI LIKE @ANI ' +
- 'AND FirstQueue LIKE @COLA ' +
- 'AND FirstAgent = @AGENTE '
- SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
- SET @QUERY = REPLACE(@QUERY, '{TABLENAME}', @TABLENAME)
- EXEC sp_executesql @QUERY,
- N'@CUSTOMERID INT, @FECHADESDE DATETIME, @FECHAHASTA DATETIME, @AGENTE INT, @ANI varchar(60), @COLA varchar(60) ',
- @CUSTOMERID = @CUSTOMERID, @FECHADESDE = @FECHADESDE, @FECHAHASTA = @FECHAHASTA, @AGENTE = @AGENTE, @ANI = @ANI, @COLA = @COLA
- END
- FETCH NEXT FROM CURSOR_TABLAS
- INTO @REPORTTYPE, @TABLENAME
- END
- CLOSE CURSOR_TABLAS
- DEALLOCATE CURSOR_TABLAS
- SET @QUERY = 'SELECT Indice
- ,tipo.Description as [Tipo de Llamada]
- ,CONVERT( VARCHAR , Calls.[CallLocalTime] , 103) as [Fecha]
- ,CONVERT( VARCHAR , Calls.[CallLocalTime] , 108 ) AS [Hora]
- ,CONVERT(char(8), DATEADD(second, calls.CallDuration, ''00:00''), 108) AS [Duracion Llamada]
- ,CONVERT(char(8), DATEADD(second, calls.[ConvDuration], ''00:00''), 108) AS [Duracion Conversacion]
- ,CONVERT(char(8), DATEADD(second, calls.[TotalWaitDuration], ''00:00''), 108) AS [Duracion Espera]
- ,CONVERT(char(8), DATEADD(second, calls.[IvrDuration], ''00:00''), 108) AS [Duracion IVR ]
- ,CONVERT(char(8), DATEADD(second, calls.[WrapupDuration], ''00:00''), 108) AS [Duracion Wrapup ]
- ,calls.ANI AS [Telefono Entrante]
- ,CASE
- WHEN (SUBSTRING(calls.[UUI],1,1)) = ''C'' THEN ''Contrato''
- WHEN (SUBSTRING(calls.[UUI],1,1)) = ''S'' THEN ''Siniestro''
- WHEN (SUBSTRING(calls.[UUI],1,1)) = ''V'' THEN ''Venta''
- WHEN (SUBSTRING(calls.[UUI],1,1)) = ''T'' THEN ''CUIT''
- WHEN (SUBSTRING(calls.[UUI],1,1)) = ''D'' THEN ''DNI''
- WHEN (SUBSTRING(calls.[UUI],1,1)) = ''A'' THEN ''Seguros''END AS ''Tipo Contacto''
- ,SUBSTRING(calls.[UUI],3,10 ) AS [Dato IVR]
- ,Cola.[Description] AS [ Cola Entrante]
- ,calls.Abandon AS [Abandonada]
- ,calls.FirstQueue AS [Cola]
- ,Calls.[OutTel] AS [Telefono Saliente]
- ,Calls.LastTransfer as [Transferencia]
- ,Calls.[FirstAgent] AS [Agente]
- ,agentes.[FirstName] +'' '' + agentes.[LastName] AS [Nombre Agente]
- ,estado.StatusText AS [Calificacion]
- ,ENC.RTA_2 AS [Respuesta 1]
- ,ENC.RTA_3 AS [Respuesta 2]
- ,ENC.RTA_4 AS [Respuesta 3]
- ,ENC.RTA_5 AS [Respuesta 4]
- ,ENC.RTA_6 AS [Respuesta NPS]
- FROM ##ODCalls_{RANDOM} AS calls
- LEFT JOIN ##Encuesta_{RANDOM} ENC ON calls.ID = ENC.CALL_ID
- LEFT JOIN [vondatt].[dbo].[CallTypes] Tipo ON calls.CallType = tipo.CallType
- LEFT JOIN [vadmint].[dbo].[ListQueues] Cola ON calls.FirstQueue = cola.[QueueID]
- LEFT JOIN [vadmint].[dbo].[ListAgents] Agentes ON Calls.[FirstAgent] = agentes.[Ident]
- LEFT JOIN [vadmint].[dbo].[ListCallStatus] estado ON calls.CallStatusNum = estado.StatusCode AND calls.CallStatusGroup = estado.StatusGroup AND calls.[CallStatusDetail] = estado.[StatusDetail]
- ORDER BY [CallLocalTime]
- '
- SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
- EXEC sp_executesql @QUERY
- SET @QUERY = 'DROP TABLE ##ODCalls_{RANDOM}'
- SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
- EXEC sp_executesql @QUERY
- SET @QUERY = 'DROP TABLE ##Encuesta_{RANDOM} '
- SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
- EXEC sp_executesql @QUERY
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement