crea_panel_final_preliminar.txt

#RODBC::sqlQuery(channel = myconn,query = "DROP  TABLE TotalUL")
#RODBC::sqlQuery(channel = myconn,query = "DROP  TABLE PanelMensual_PF")
#RODBC::sqlQuery(channel = myconn,query = "DROP  TABLE VentasMesFP")
#RODBC::sqlQuery(channel = myconn,query = "DROP  TABLE CodRec_2018P")
#RODBC::sqlQuery(channel = myconn,query = "DROP  TABLE CuentaOrigP")
#RODBC::sqlQuery(channel = myconn,query = "DROP  TABLE CuentaRectP")
#RODBC::sqlQuery(channel = myconn,query = "DROP  TABLE NoCoincidentes")

bd<-RODBC::sqlQuery(channel = myconn,query = "select * from VentasMesFP")

#/* UNIR UNIDADES TOTALES
#IMPORTAR AMBAS TABLAS DE CUENTA UL. FINAL (TotalULF)Y PRELIMINAR (TotalULP). 
#SOLO LAS NOMINAS TOTALES CONTIENEN RUT DE PRUEBA.*/
  
#  /*Tiempo total 00:04:15*/
  
#  /*1. Identificar UL no coincidentes de preliminar (B) en final (A).*/
RODBC::sqlQuery(channel = myconn,query = "
SELECT  TotalULP.Rut_ficticio, TotalULP.Orig, TotalULP.Rect, TotalULP.Total_Decl, TotalULP.Observacion
INTO    NoCoincidentes
FROM    TotalULP LEFT OUTER JOIN TotalULF ON TotalULP.Rut_ficticio = TotalULF.Rut_ficticio
WHERE   (TotalULF.Rut_ficticio IS NULL)
GROUP BY TotalULP.Rut_ficticio, TotalULP.Orig, TotalULP.Rect, TotalULP.Total_Decl, TotalULP.Observacion
")

#/*2. Crear tabla "TotalUL" como copia de "TotalULF" para crear Final y Preliminar.*/

RODBC::sqlQuery(channel = myconn,query = " 
 SELECT Rut_ficticio, Orig, Rect, Total_Decl, Observacion
INTO  TotalUL FROM TotalULF
")

#/*3. A UL coincidentes entre A y B, actualizar cuentas con la suma.*/
 
RODBC::sqlQuery(channel = myconn,query = "
 UPDATE  TotalUL
SET     Orig = TotalULF.Orig + TotalULP.Orig, Rect = TotalULF.Rect + TotalULP.Rect, Total_Decl = TotalULF.Total_Decl + TotalULP.Total_Decl
FROM    TotalUL INNER JOIN TotalULF ON TotalUL.Rut_ficticio = TotalULF.Rut_ficticio INNER JOIN
TotalULP ON TotalULF.Rut_ficticio = TotalULP.Rut_ficticio
")

#/*4. Insertar a C registros no coincidentes.*/
RODBC::sqlQuery(channel = myconn,query = " 
INSERT INTO TotalUL
(Rut_ficticio, Orig, Rect, Total_Decl, Observacion)
SELECT     Rut_ficticio, Orig, Rect, Total_Decl, Observacion
FROM         NoCoincidentes
")

#/*5. ELABORACION PANEL UNICO. FINAL MÁS PRELIMINAR. IMPORTAR AMBOS PANELES FINALIZADOS.*/
#/*A. Crear copia del panel final*/
  
  RODBC::sqlQuery(channel = myconn,query = "
  SELECT  Tipo, Origen, Rut_ficticio, Mes_trib, Año_trib, Procedencia, FechaPresentacion, Mes_dato, Orden, Folio, FechaProceso, cod20, cod108, cod111, cod112, cod142, 
cod152, cod501, cod502, cod510, cod513, cod529, cod587, cod709, cod720, cod732, cod759, Seleccion, Criterio, Caso_mensual, Venta_mensual, Suma_codigos
INTO    PanelMensual_PF FROM  PanelMensualF
")

#/*B. Eliminar declaraciones preliminares - DIC*/
#RODBC::sqlQuery(channel = myconn,query = " 
#DELETE FROM PanelMensual_PF WHERE (Mes_trib = 12)
#")

/*C.Insertar declaraciones panel preliminar Mes_Trib=1*/

RODBC::sqlQuery(channel = myconn,query = " 
 INSERT INTO PanelMensual_PF
(Tipo, Origen, Rut_ficticio, Mes_trib, Año_trib, Procedencia, FechaPresentacion, Mes_dato, Orden, Folio, FechaProceso, cod20, cod108, cod111, cod112, cod142, 
  cod152, cod501, cod502, cod510, cod513, cod529, cod587, cod709, cod720, cod732, cod759, Seleccion, Criterio, Caso_mensual, Venta_mensual, Suma_codigos)
SELECT     Tipo, Origen, Rut_ficticio, Mes_trib, Año_trib, Procedencia, FechaPresentacion, Mes_dato, Orden, Folio, FechaProceso, cod20, cod108, cod111, cod112, cod142, 
cod152, cod501, cod502, cod510, cod513, cod529, cod587, cod709, cod720, cod732, cod759, Seleccion, Criterio, Caso_mensual, Venta_mensual, Suma_codigos
FROM       PanelMensualP
WHERE     (Mes_trib = 1)
")

#/*6. Crear tabla RUT - Venta Mensual*/

  RODBC::sqlQuery(channel = myconn,query = "
    SELECT  Rut_ficticio INTO VentasMesFP FROM PanelMensual_PF GROUP BY Seleccion, Rut_ficticio
HAVING  (Seleccion = N'SI')
")

#/*7. Crear campos ventas mensuales en tabla de ventas*/

RODBC::sqlQuery(channel = myconn,query = "
  ALTER TABLE VentasMesFP ADD ENE float;
ALTER TABLE VentasMesFP ADD FEB float;
ALTER TABLE VentasMesFP ADD MAR float;
ALTER TABLE VentasMesFP ADD ABR float;
ALTER TABLE VentasMesFP ADD MAY float;
ALTER TABLE VentasMesFP ADD JUN float;
ALTER TABLE VentasMesFP ADD JUL float;
ALTER TABLE VentasMesFP ADD AGO float
ALTER TABLE VentasMesFP ADD SEP float;
ALTER TABLE VentasMesFP ADD OCT float;
ALTER TABLE VentasMesFP ADD NOV float;
ALTER TABLE VentasMesFP ADD DIC float;
ALTER TABLE VentasMesFP ADD ENE19 float;
")

#/*8. Actualizar valor de ventas mensuales con pibote*/
  
RODBC::sqlQuery(channel = myconn,query = " 
 UPDATE  VentasMesFP SET ENE = PanelMensual_PF.Venta_mensual
FROM    VentasMesFP INNER JOIN PanelMensual_PF ON VentasMesFP.Rut_ficticio = PanelMensual_PF.Rut_ficticio
WHERE   (PanelMensual_PF.Mes_trib = 1) AND  (PanelMensual_PF.Año_trib = 2018) AND (PanelMensual_PF.Seleccion = N'SI') AND (VentasMesFP.ENE IS NULL)
")

RODBC::sqlQuery(channel = myconn,query = "
UPDATE  VentasMesFP SET FEB = PanelMensual_PF.Venta_mensual
FROM    VentasMesFP INNER JOIN PanelMensual_PF ON VentasMesFP.Rut_ficticio = PanelMensual_PF.Rut_ficticio
WHERE   (PanelMensual_PF.Mes_trib = 2) AND (PanelMensual_PF.Seleccion = N'SI') AND (VentasMesFP.FEB IS NULL)
")

RODBC::sqlQuery(channel = myconn,query = "
UPDATE  VentasMesFP SET MAR = PanelMensual_PF.Venta_mensual
FROM    VentasMesFP INNER JOIN PanelMensual_PF ON VentasMesFP.Rut_ficticio = PanelMensual_PF.Rut_ficticio
WHERE   (PanelMensual_PF.Mes_trib = 3) AND (PanelMensual_PF.Seleccion = N'SI') AND (VentasMesFP.MAR IS NULL)
")

RODBC::sqlQuery(channel = myconn,query = "
UPDATE  VentasMesFP SET ABR = PanelMensual_PF.Venta_mensual
FROM    VentasMesFP INNER JOIN PanelMensual_PF ON VentasMesFP.Rut_ficticio = PanelMensual_PF.Rut_ficticio
WHERE   (PanelMensual_PF.Mes_trib = 4) AND (PanelMensual_PF.Seleccion = N'SI') AND (VentasMesFP.ABR IS NULL)
")

RODBC::sqlQuery(channel = myconn,query = "
UPDATE  VentasMesFP SET MAY = PanelMensual_PF.Venta_mensual
FROM    VentasMesFP INNER JOIN PanelMensual_PF ON VentasMesFP.Rut_ficticio = PanelMensual_PF.Rut_ficticio
WHERE   (PanelMensual_PF.Mes_trib = 5) AND (PanelMensual_PF.Seleccion = N'SI') AND (VentasMesFP.MAY IS NULL)
")

RODBC::sqlQuery(channel = myconn,query = "
UPDATE  VentasMesFP SET JUN = PanelMensual_PF.Venta_mensual
FROM    VentasMesFP INNER JOIN PanelMensual_PF ON VentasMesFP.Rut_ficticio = PanelMensual_PF.Rut_ficticio
WHERE   (PanelMensual_PF.Mes_trib = 6) AND (PanelMensual_PF.Seleccion = N'SI') AND (VentasMesFP.JUN IS NULL)
")

RODBC::sqlQuery(channel = myconn,query = "
UPDATE  VentasMesFP SET JUL = PanelMensual_PF.Venta_mensual
FROM    VentasMesFP INNER JOIN PanelMensual_PF ON VentasMesFP.Rut_ficticio = PanelMensual_PF.Rut_ficticio
WHERE   (PanelMensual_PF.Mes_trib = 7) AND (PanelMensual_PF.Seleccion = N'SI') AND (VentasMesFP.JUL IS NULL)
")

RODBC::sqlQuery(channel = myconn,query = "
UPDATE  VentasMesFP SET AGO = PanelMensual_PF.Venta_mensual
FROM    VentasMesFP INNER JOIN PanelMensual_PF ON VentasMesFP.Rut_ficticio = PanelMensual_PF.Rut_ficticio
WHERE   (PanelMensual_PF.Mes_trib = 8) AND (PanelMensual_PF.Seleccion = N'SI') AND (VentasMesFP.AGO IS NULL)
")

RODBC::sqlQuery(channel = myconn,query = "
UPDATE  VentasMesFP SET SEP = PanelMensual_PF.Venta_mensual
FROM    VentasMesFP INNER JOIN PanelMensual_PF ON VentasMesFP.Rut_ficticio = PanelMensual_PF.Rut_ficticio
WHERE   (PanelMensual_PF.Mes_trib = 9) AND (PanelMensual_PF.Seleccion = N'SI') AND (VentasMesFP.SEP IS NULL)
")

RODBC::sqlQuery(channel = myconn,query = "
UPDATE  VentasMesFP SET OCT = PanelMensual_PF.Venta_mensual
FROM    VentasMesFP INNER JOIN PanelMensual_PF ON VentasMesFP.Rut_ficticio = PanelMensual_PF.Rut_ficticio
WHERE   (PanelMensual_PF.Mes_trib = 10) AND (PanelMensual_PF.Seleccion = N'SI') AND (VentasMesFP.OCT IS NULL)
")

RODBC::sqlQuery(channel = myconn,query = "
UPDATE  VentasMesFP SET NOV = PanelMensual_PF.Venta_mensual
FROM    VentasMesFP INNER JOIN PanelMensual_PF ON VentasMesFP.Rut_ficticio = PanelMensual_PF.Rut_ficticio
WHERE   (PanelMensual_PF.Mes_trib = 11) AND (PanelMensual_PF.Seleccion = N'SI') AND (VentasMesFP.NOV IS NULL)
")

RODBC::sqlQuery(channel = myconn,query = "
UPDATE  VentasMesFP SET DIC = PanelMensual_PF.Venta_mensual
FROM	VentasMesFP INNER JOIN PanelMensual_PF ON VentasMesFP.Rut_ficticio = PanelMensual_PF.Rut_ficticio
WHERE   (PanelMensual_PF.Mes_trib = 12) AND (PanelMensual_PF.Seleccion = N'SI') AND (VentasMesFP.DIC IS NULL)
")

RODBC::sqlQuery(channel = myconn,query = "
UPDATE  VentasMesFP SET ENE19 = PanelMensual_PF.Venta_mensual
FROM    VentasMesFP INNER JOIN PanelMensual_PF ON VentasMesFP.Rut_ficticio = PanelMensual_PF.Rut_ficticio
WHERE   (PanelMensual_PF.Mes_trib = 1) AND  (PanelMensual_PF.Año_trib = 2019) AND (PanelMensual_PF.Seleccion = N'SI') AND (VentasMesFP.ENE19 IS NULL)
")

#/*9. Insertar unidades totales no coincidentes con panel*/
RODBC::sqlQuery(channel = myconn,query = "
  INSERT INTO VentasMesFP (Rut_ficticio) SELECT TotalUL.Rut_ficticio
FROM       TotalUL LEFT OUTER JOIN VentasMesFP AS VentasMesFP_1 ON TotalUL.Rut_ficticio = VentasMesFP_1.Rut_ficticio
WHERE      (VentasMesFP_1.Rut_ficticio IS NULL) AND (TotalUL.Observacion <> 'EJERCICIO DE PRUEBA' OR TotalUL.Observacion IS NULL)
")

RODBC::sqlQuery(channel = myconn,query = "
DROP TABLE NoCoincidentes
")