DECLARE @CUSTOMERID INT = 1
    DECLARE @RANDOM VARCHAR(12) = CAST(CAST(RAND() * 1000000000 AS INT) AS VARCHAR)
    DECLARE @FECHADESDE DATE = '20200128'
    DECLARE @FECHAHASTA DATE = '20200129'
    DECLARE @AGENTE VARCHAR(500) = ';-1;'
    DECLARE @COLA VARCHAR(500) =';560;'
    DECLARE @ABANDON VARCHAR(500) = ';1;0;'
    DECLARE @ANI VARCHAR(60) =''
    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_LAM079\SQLEXPERTA_SQLEXPERTA_VONDATHP].[VONDATHP].DBO.VWINBOUNDCALLS', '[VONDATHP]..ODCalls'), 'VWINBOUNDCALLS', 'ODCalls')
    FROM vreportp.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 [vcampap]..[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 CallType != 5 '+
                       'AND ANI LIKE ''%'' + @ANI + ''%'' ' +
                           'AND (@ABANDON LIKE CONCAT(''%;%'' , abandon , '';%'') ) ' +
                       ' AND (@AGENTE = '';-1;'' OR @AGENTE LIKE CONCAT(''%;%'' , LastAgent , '';%'') )' +
                       ' AND (@COLA = '';-1;'' OR @COLA LIKE CONCAT(''%;%'' , LastQueue , '';%'') )' +
                       'AND CallLocalTime <= @FECHAHASTA ' +
                       'AND CallLocalTime >= @FECHADESDE '

  SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
      SET @QUERY = REPLACE(@QUERY, '{TABLENAME}', @TABLENAME)
      EXEC sp_executesql @QUERY,
           N'@CUSTOMERID INT, @FECHADESDE DATETIME, @FECHAHASTA DATETIME, @AGENTE VARCHAR(500),@COLA VARCHAR(500),@ABANDON VARCHAR(500),@ANI VARCHAR(60)',
           @CUSTOMERID = @CUSTOMERID, @FECHADESDE = @FECHADESDE, @FECHAHASTA = @FECHAHASTA, @AGENTE = @AGENTE, @COLA = @COLA , @ABANDON = @ABANDON,@ANI = @ANI
    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]
, CASE
    WHEN calls.Abandon = 0 THEN ''NO''
    WHEN calls.Abandon = 1 THEN ''SI'' END AS Abandonada2
,Calls.LastQueue AS [Cola Final]
,calls.FirstQueue AS [Cola Inicial]
,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 [vondatp].[dbo].[CallTypes] Tipo ON calls.CallType = tipo.CallType
LEFT JOIN [vadminp].[dbo].[ListQueues] Cola ON calls.FirstQueue = cola.[QueueID] AND COLA.CustomerID = @CUSTOMERID
LEFT JOIN [vadminp].[dbo].[ListAgents] Agentes ON  Calls.[LastAgent] = agentes.[Ident] AND Agentes.CustomerID = @CUSTOMERID
LEFT JOIN [vadminp].[dbo].[ListCallStatus] estado ON calls.CallStatusNum = estado.StatusCode AND calls.CallStatusGroup = estado.StatusGroup AND calls.[CallStatusDetail] = estado.[StatusDetail] AND estado.CustomerID = @CUSTOMERID
ORDER BY  [CallLocalTime]
'

     SET @QUERY = REPLACE(@QUERY, '{RANDOM}', @RANDOM)
    EXEC sp_executesql @QUERY,
N'@CUSTOMERID INT',
@CUSTOMERID = @CUSTOMERID

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