RE:Error con instruccion SQL
Claro, tienes razón, este es el sub que realiza las consultas
Sub Consultas()
Dim texto_sql, texto_sql2, texto_sql3, texto_sql4, texto_sql5, texto_sql6 As Variant
Inicial = Sheets("Menú").DTPickerInicial.Value
mes = CInt(Mid(Inicial, 4, 2))
anio = CInt(Mid(Inicial, 7, 4))
Let texto_sql = "DECLARE @PERIODO AS INT, @CAMPANA AS INT, @ANIO AS INT SET @PERIODO = " & mes & " SET @CAMPANA = " & codcampana & " SET @ANIO = " & anio & ""
Let texto_sql2 = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TBL_ARCHIVO_SAP]') AND type in (N'U'))" _
& "DROP TABLE [dbo].[TBL_ARCHIVO_SAP]"
Let texto_sql3 = "CREATE TABLE [dbo].[TBL_ARCHIVO_SAP]([CONSECUTIVO] [INT] IDENTITY (1,1),[NEG_NID_NEGOCIO] [int] NOT NULL, [ID_CAMPAÑA] [int] NULL, [CAL_DFECHA_FINAL] [datetime] NULL, [CRI_NID_CRITERIO] [int] NOT NULL," _
& "[ID_BASE_INGRESO] [int] NULL,"
Let texto_sql4 = "[Tipo_Proceso] [varchar](3) NOT NULL, [Cliente] [varchar](50) NULL, [Responsable_Venta] [int] NULL, [Moneda_Facturacion] [varchar](10) NULL, [TRM_Pactada] [int] NULL, [Unidad_Negocio] [varchar](2) NULL," _
& "[Fecha_Gestion] [varchar](92) NULL,"
Let texto_sql5 = "[Codigo_Negocio] [varchar](9) NOT NULL, [Nombre_Negocio] [varchar](50) NULL, [Cantidad] [float] NULL, [Unidad_Medida] [varchar](2) NOT NULL, [Valor_Unitario] [money] NULL, [Tipo_Ingreso] [varchar](2) NOT NULL," _
& "[Codigo_Campaña] [varchar](31) NULL,"
Let texto_sql6 = "[Base_Ingreso] [varchar](2) NULL, [Nom_Archi_Carga] [varchar](127) NULL, [Numero_Llamadas] [varchar](39) NULL) ON [PRIMARY]"
' Let texto_completo = texto_sql & texto_sql2 & texto_sql3 & texto_sql4 & texto_sql5 & texto_sql6
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=colbogvmdb02;Use Procedure for Prepare=1;Aut" _
, _
"o Translate=True;Packet Size=4096;Workstation ID=Automatizacion1;Use Encryption for Data=False;Tag with column collation when po" _
, "ssible=False;Initial Catalog=ATENTO_CONTROL_GESTION"), Destination:=Range("$A$1")).QueryTable
.CommandText = texto_sql & texto_sql2 & texto_sql3 & texto_sql4 & texto_sql5 & texto_sql6
.Refresh BackgroundQuery:=False
End With
' **********************************************
' HASTA ACA VIENE LO QUE HE PROBADO
' **********************************************
Let texto_sql2 = "INSERT INTO TBL_ARCHIVO_SAP SELECT NEG.NEG_NID_NEGOCIO,ID_CAMPAÑA,CAL.CAL_DFECHA_FINAL,NEG.CRI_NID_CRITERIO,ID_BASE_INGRESO," _
& "CASE WHEN ID_BASE_INGRESO IN (3,5,10) THEN 'I01' ELSE 'I02' END AS Tipo_Proceso, CLI.CLI_CIDENTIFICACION AS Cliente, GER_NID_GERENTE AS Responsable_Venta, CAM_CTIPO_MONEDA AS Moneda_Facturacion, CASE WHEN CAM_NTASA_PACTADA <> 0 THEN CAM_NTASA_PACTADA ELSE 0 END AS TRM_Pactada," _
& "RIGHT('00'+CAST(ID_UNIDAD_NEGOCIO AS VARCHAR),2)AS Unidad_Negocio, CASE WHEN LEN(CAST(DAY(CAL.CAL_DFECHA_FINAL)AS VARCHAR)) = 1 THEN '0'+ CAST(DAY(CAL.CAL_DFECHA_FINAL)AS VARCHAR)ELSE CAST(DAY(CAL.CAL_DFECHA_FINAL)AS VARCHAR) END + CASE WHEN LEN(CAST(MONTH(CAL.CAL_DFECHA_FINAL)AS VARCHAR)) = 1 THEN '0'+ CAST(MONTH(CAL.CAL_DFECHA_FINAL)AS VARCHAR)ELSE CAST(DAY(CAL.CAL_DFECHA_FINAL)AS VARCHAR)END + " _
& "CAST(YEAR(CAL.CAL_DFECHA_FINAL)AS VARCHAR) AS Fecha_Gestion, CASE WHEN ID_BASE_INGRESO = 11 THEN '100000001' ELSE '100000002'END AS Codigo_Negocio, NEG_CDESCRIPCION AS Nombre_Negocio, CASE WHEN ID_BASE_INGRESO = 3 THEN CANTIDAD_ACUMULADA ELSE CANTIDAD_ACTUAL END AS Cantidad," _
& "'UN' as Unidad_Medida, CASE WHEN ID_BASE_INGRESO = 3 THEN TARIFA_ACUMULADA ELSE TARIFA_ACTUAL END AS Valor_Unitario, CASE WHEN ID_BASE_INGRESO = 11 THEN '00' ELSE '01'END AS Tipo_Ingreso, 'C'+CAST(CAM.CAM_NID_CAMPANA AS VARCHAR) AS Codigo_Campaña, RIGHT('00'+CAST(FAC.ID_BASE_INGRESO AS VARCHAR),2) AS Base_Ingreso," _
& "CASE WHEN LEN(CAST(DAY(CAL.CAL_DFECHA_FINAL)AS VARCHAR)) = 1 THEN '0'+ CAST(DAY(CAL.CAL_DFECHA_FINAL)AS VARCHAR)ELSE CAST(DAY(CAL.CAL_DFECHA_FINAL)AS VARCHAR) END + CASE WHEN LEN(CAST(MONTH(CAL.CAL_DFECHA_FINAL)AS VARCHAR)) = 1 THEN '0'+ CAST(MONTH(CAL.CAL_DFECHA_FINAL)AS VARCHAR)ELSE CAST(DAY(CAL.CAL_DFECHA_FINAL)AS VARCHAR)END + CAST(YEAR(CAL.CAL_DFECHA_FINAL)AS VARCHAR)+'C'+CAST(CAM.CAM_NID_CAMPANA AS VARCHAR) + RIGHT('0000'+CAST(NEG.CRI_NID_CRITERIO AS VARCHAR),4)AS Nom_Archi_Carga," _
& "CASE WHEN ID_BASE_INGRESO = 6 THEN CAST(CANTIDAD_ACTUAL AS VARCHAR)+' Llamadas' ELSE '0'+' Llamadas' END AS Numero_Llamadas FROM TBL_DW_FACT_INGRESO FAC INNER JOIN TBL_TNEGOCIO NEG ON FAC.NEG_NID_NEGOCIO = NEG.NEG_NID_NEGOCIO LEFT JOIN TBL_TCLIENTE CLI ON FAC.ID_CLIENTE = CLI.CLI_NID_CLIENTE LEFT JOIN TBL_TCAMPANA CAM ON FAC.ID_CAMPAÑA = CAM.CAM_NID_CAMPANA LEFT JOIN TBL_TCALENDARIO_CAMPANA CAL ON FAC.ID_CAMPAÑA = CAL.CAM_NID_CAMPANA WHERE CAL_NMES = @PERIODO AND CAL_NANIO = @ANIO AND FAC.ID_CAMPAÑA = @CAMPANA " _
& "order by fecha_gestion asc SELECT cast(Consecutivo as varchar)+'0' Posicion, Tipo_Proceso, cliente, Responsable_Venta, Moneda_Facturacion, CASE WHEN TRM_Pactada = 0 THEN CAST('' AS VARCHAR) ELSE CAST(TRM_Pactada AS VARCHAR) END AS TRM_Pactada, Unidad_Negocio, Fecha_Gestion, Codigo_Negocio, Nombre_Negocio, REPLACE(ROUND(SUM(Cantidad),3),',','.') AS Cantidad, Unidad_Medida, REPLACE(SUM(Valor_Unitario),',','.') AS Valor_Unitario, Tipo_Ingreso, Codigo_Campaña, Base_Ingreso," _
& "Nom_Archi_Carga, CASE WHEN Numero_Llamadas = '0 Llamadas' THEN '' ELSE Numero_Llamadas END AS Numero_Llamadas, CASE WHEN ID_BASE_INGRESO = 6 THEN CAST('TMO ' AS VARCHAR)+CAST(FAC_IN.TMO AS VARCHAR)+CAST(' Minutos' AS VARCHAR) ELSE '' END AS TMO ,MONTH(cal_dfecha_final)AS Mes_Gestion FROM TBL_ARCHIVO_SAP INNER JOIN (SELECT ID_CAMPANA,(SUM(ACWTIME_SKILL)+SUM(ACDTIME)+SUM(HOLDTIME))/SUM(LLAMADAS_RECIBIDAS) AS TMO FROM TBL_DW_FACT_INBOUND FAC LEFT JOIN TBL_TCALENDARIO_CAMPANA CAL ON FAC.ID_CAMPANA = CAL.CAM_NID_CAMPANA" _
& "WHERE MONTH(FECHA_PROCESO)=@PERIODO and YEAR(FECHA_PROCESO)= @ANIO AND ID_CAMPANA = @CAMPANA GROUP BY ID_CAMPANA)AS FAC_IN ON TBL_ARCHIVO_SAP.ID_CAMPAÑA = FAC_IN.ID_CAMPANA GROUP BY NEG_NID_NEGOCIO, consecutivo, Tipo_proceso, cliente, Responsable_Venta, Moneda_Facturacion, TRM_Pactada, Unidad_Negocio, Fecha_Gestion, Codigo_Negocio, Nombre_Negocio, Unidad_Medida, Tipo_Ingreso, Codigo_Campaña, Base_Ingreso, Nom_Archi_Carga, Numero_Llamadas , FAC_IN.TMO, CRI_NID_CRITERIO, CAL_DFECHA_FINAL, ID_BASE_INGRESO ORDER BY NEG_NID_NEGOCIO"
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=colbogvmdb02;Use Procedure for Prepare=1;Aut" _
, _
"o Translate=True;Packet Size=4096;Workstation ID=Automatizacion1;Use Encryption for Data=False;Tag with column collation when po" _
, "ssible=False;Initial Catalog=ATENTO_CONTROL_GESTION"), Destination:=Range("$A$1")).QueryTable
.CommandText = texto_sql2
.Refresh BackgroundQuery:=False
End With
Sheets("DATOS").Select
ActiveWindow.SelectedSheets.Visible = False
End Sub