Skip to content

Instantly share code, notes, and snippets.

@rparatodxs

rparatodxs/venta_final.txt

Last active March 1, 2019 11:56
Show Gist options
  • Save rparatodxs/ff33d99f7c54ab9824d6281b5af3367c to your computer and use it in GitHub Desktop.
Save rparatodxs/ff33d99f7c54ab9824d6281b5af3367c to your computer and use it in GitHub Desktop.
Download ZIP
venta_final
Raw
venta_final.txt
rm(list=ls())
library(RODBC)
library(sqldf)
myconn <-odbcConnect("validacion_karina")
sqlTables(myconn , tableType = "TABLE")
#RODBC::sqlQuery(channel = myconn,query = "DROP TABLE recF")
#RODBC::sqlQuery(channel = myconn,query = "DROP TABLE oriF")
#RODBC::sqlQuery(channel = myconn,query = "DROP TABLE CodOri_2018F")
#RODBC::sqlQuery(channel = myconn,query = "DROP TABLE CodRec_2018F")
#RODBC::sqlQuery(channel = myconn,query = "DROP TABLE CuentaOrigF")
#RODBC::sqlQuery(channel = myconn,query = "DROP TABLE CuentaRectF")
#RODBC::sqlQuery(channel = myconn,query = "DROP TABLE TotalULF")
#RODBC::sqlQuery(channel = myconn,query = "DROP TABLE PanelMensualF")
#RODBC::sqlQuery(channel = myconn,query = "DROP TABLE RutF_MesTF")
bd<-RODBC::sqlQuery(channel = myconn,query = "select * from CodRec_2018F")
#/*Tiempo de ejecucion 00:37:14*/
sqlQuery(channel = myconn,query = " SELECT Año_trib, Rut_ficticio, COUNT(Orden) AS Expr1
INTO oriF
FROM DEEC_4.dbo.F292018_Originales_Id
GROUP BY Año_trib, Rut_ficticio
HAVING (Año_trib = 2018)
")
sqlQuery(channel = myconn,query = "
SELECT Año_trib, Rut_ficticio, COUNT(Orden) AS Expr1
INTO recF
FROM DEEC_4.dbo.F292018_Rectif_Originales_Id
GROUP BY Año_trib, Rut_ficticio
HAVING (Año_trib = 2018)
")
sqlQuery(channel = myconn,query = "
ALTER TABLE oriF ADD Origen nvarchar(4)
ALTER TABLE recF ADD Origen nvarchar(4)
")
sqlQuery(channel = myconn,query = "
UPDATE oriF SET Origen = N'ORI' WHERE (Origen IS NULL)
UPDATE recF SET Origen = N'REC' WHERE (Origen IS NULL)
")
sqlQuery(channel = myconn,query = "
INSERT INTO oriF (Año_trib, Rut_ficticio, Expr1, Origen)
SELECT Año_trib, Rut_ficticio, Expr1, Origen
FROM recF
")
sqlQuery(channel = myconn,query = "
SELECT Rut_ficticio INTO TotalULF
FROM oriF GROUP BY Rut_ficticio
")
sqlQuery(channel = myconn,query = "
ALTER TABLE TotalULF ADD Orig int;
ALTER TABLE TotalULF ADD Rect int;
ALTER TABLE TotalULF ADD Total_Decl int;
ALTER TABLE TotalULF ADD Observacion varchar (50);
")
sqlQuery(channel = myconn,query = "
UPDATE TotalULF SET Orig = oriF.Expr1
FROM TotalULF INNER JOIN oriF ON TotalULF.Rut_ficticio = oriF.Rut_ficticio
WHERE (oriF.Origen = N'ORI') AND (TotalULF.Orig IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE TotalULF SET Rect = oriF.Expr1
FROM TotalULF INNER JOIN oriF ON TotalULF.Rut_ficticio = oriF.Rut_ficticio
WHERE (oriF.Origen = N'REC') AND (TotalULF.Rect IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE TotalULF SET Total_Decl = ISNULL(Orig,0) + ISNULL(Rect,0)
")
sqlQuery(channel = myconn,query = "
UPDATE TotalULF SET Observacion = [02_Final_fic].Tipo
FROM TotalULF INNER JOIN [02_Final_fic] ON TotalULF.Rut_ficticio = [02_Final_fic].Rut_ficticio_prueba
WHERE (TotalULF.Observacion IS NULL)
")
sqlQuery(channel = myconn,query = "
SELECT Observacion, COUNT(Rut_ficticio) AS Expr1, SUM(Orig) AS Expr2, SUM(Rect) AS Expr3, SUM(Total_Decl) AS Expr4
FROM TotalULF GROUP BY Observacion
")
sqlQuery(channel = myconn,query = "
SELECT Rut_ficticio, Orig, Rect, Total_Decl, Observacion
FROM TotalULF WHERE (Observacion = 'EJERCICIO DE PRUEBA')
")
sqlQuery(channel = myconn,query = "
SELECT DEEC_4.dbo.F292018_Originales_Id.Rut_ficticio,
DEEC_4.dbo.F292018_Originales_Id.Mes_trib, DEEC_4.dbo.F292018_Originales_Id.Año_trib,
DEEC_4.dbo.F292018_Originales_Id.Procedencia, DEEC_4.dbo.F292018_Originales_Id.FechaPresentacion, DEEC_4.dbo.F292018_Originales_Id.Mes_dato,
DEEC_4.dbo.F292018_Originales_Id.Orden, DEEC_4.dbo.F292018_Originales_Datos.Codigo, DEEC_4.dbo.F292018_Originales_Datos.Valor,
DEEC_4.dbo.F292018_Originales_Id.Folio, DEEC_4.dbo.F292018_Originales_Id.FechaProceso
INTO CodOri_2018F
FROM DEEC_4.dbo.F292018_Originales_Id INNER JOIN DEEC_4.dbo.F292018_Originales_Datos ON DEEC_4.dbo.F292018_Originales_Id.Mes_dato = DEEC_4.dbo.F292018_Originales_Datos.Mes_dato AND
DEEC_4.dbo.F292018_Originales_Id.Orden = DEEC_4.dbo.F292018_Originales_Datos.Orden
WHERE (DEEC_4.dbo.F292018_Originales_Id.Año_trib = 2018) AND ((DEEC_4.dbo.F292018_Originales_Datos.Codigo = 20 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 142 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 587 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 720 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 732 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 502 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 111 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 513 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 108 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 510 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 709 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 112 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 501 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 152 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 529 OR
DEEC_4.dbo.F292018_Originales_Datos.Codigo = 759))
")
sqlQuery(channel = myconn,query = "
SELECT DEEC_4.dbo.F292018_Rectif_Originales_Id.Rut_ficticio, DEEC_4.dbo.F292018_Rectif_Originales_Id.Mes_trib, DEEC_4.dbo.F292018_Rectif_Originales_Id.Año_trib,
DEEC_4.dbo.F292018_Rectif_Originales_Id.Procedencia, DEEC_4.dbo.F292018_Rectif_Originales_Id.FechaPresentacion, DEEC_4.dbo.F292018_Rectif_Originales_Id.Mes_dato,
DEEC_4.dbo.F292018_Rectif_Originales_Id.Orden, DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo, DEEC_4.dbo.F292018_Rectif_Originales_Datos.Valor,
DEEC_4.dbo.F292018_Rectif_Originales_Id.Folio, DEEC_4.dbo.F292018_Rectif_Originales_Id.FechaProceso
INTO CodRec_2018F
FROM DEEC_4.dbo.F292018_Rectif_Originales_Id INNER JOIN DEEC_4.dbo.F292018_Rectif_Originales_Datos ON
DEEC_4.dbo.F292018_Rectif_Originales_Id.Mes_dato = DEEC_4.dbo.F292018_Rectif_Originales_Datos.Mes_dato AND
DEEC_4.dbo.F292018_Rectif_Originales_Id.Orden = DEEC_4.dbo.F292018_Rectif_Originales_Datos.Orden
WHERE (DEEC_4.dbo.F292018_Rectif_Originales_Id.Año_trib = 2018) AND ((DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 20 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 142 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 587 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 720 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 732 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 502 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 111 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 513 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 108 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 510 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 709 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 112 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 501 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 152 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 529 OR
DEEC_4.dbo.F292018_Rectif_Originales_Datos.Codigo = 759))
")
sqlQuery(channel = myconn,query = "
ALTER TABLE CodOri_2018F ADD Origen nvarchar(4)
ALTER TABLE CodRec_2018F ADD Origen nvarchar(4)
ALTER TABLE CodOri_2018F ADD Tipo nvarchar(5)
ALTER TABLE CodRec_2018F ADD Tipo nvarchar(5)
")
sqlQuery(channel = myconn,query = "
UPDATE CodOri_2018F SET Origen = N'ORI' WHERE (Origen IS NULL)
UPDATE CodRec_2018F SET Origen = N'REC' WHERE (Origen IS NULL)
UPDATE CodOri_2018F SET Tipo = N'FINAL' WHERE (Tipo IS NULL)
UPDATE CodRec_2018F SET Tipo = N'FINAL' WHERE (Tipo IS NULL)
")
sqlQuery(channel = myconn,query = "
INSERT INTO CodOri_2018F (Rut_ficticio, Mes_trib, Año_trib, Procedencia, FechaPresentacion, Mes_dato, Orden, Codigo, Valor, Folio, FechaProceso, Origen, Tipo)
SELECT Rut_ficticio, Mes_trib, Año_trib, Procedencia, FechaPresentacion, Mes_dato, Orden, Codigo, Valor, Folio, FechaProceso, Origen, Tipo
FROM CodRec_2018F
")
sqlQuery(channel = myconn,query = "
SELECT Tipo, Origen, Rut_ficticio, Mes_trib, Año_trib, Procedencia, FechaPresentacion, Mes_dato, Orden, Folio, FechaProceso
INTO PanelMensualF
FROM CodOri_2018F
GROUP BY Tipo, Origen, Rut_ficticio, Mes_trib, Año_trib, Procedencia, FechaPresentacion, Mes_dato, Orden, Folio, FechaProceso
")
sqlQuery(channel = myconn,query = "
ALTER TABLE PanelMensualF ADD cod20 float;
ALTER TABLE PanelMensualF ADD cod108 float;
ALTER TABLE PanelMensualF ADD cod111 float;
ALTER TABLE PanelMensualF ADD cod112 float;
ALTER TABLE PanelMensualF ADD cod142 float;
ALTER TABLE PanelMensualF ADD cod152 float;
ALTER TABLE PanelMensualF ADD cod501 float;
ALTER TABLE PanelMensualF ADD cod502 float;
ALTER TABLE PanelMensualF ADD cod510 float;
ALTER TABLE PanelMensualF ADD cod513 float;
ALTER TABLE PanelMensualF ADD cod529 float;
ALTER TABLE PanelMensualF ADD cod587 float;
ALTER TABLE PanelMensualF ADD cod709 float;
ALTER TABLE PanelMensualF ADD cod720 float;
ALTER TABLE PanelMensualF ADD cod732 float;
ALTER TABLE PanelMensualF ADD cod759 float;
ALTER TABLE PanelMensualF ADD Seleccion nvarchar(50);
ALTER TABLE PanelMensualF ADD Criterio nvarchar(100);
ALTER TABLE PanelMensualF ADD Caso_mensual nvarchar(50);
ALTER TABLE PanelMensualF ADD Venta_mensual float;
ALTER TABLE PanelMensualF ADD Suma_codigos float;
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod20 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 20) AND (PanelMensualF.cod20 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod108 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 108) AND (PanelMensualF.cod108 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod111 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 111) AND (PanelMensualF.cod111 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod112 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 112) AND (PanelMensualF.cod112 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod142 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 142) AND (PanelMensualF.cod142 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod152 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 152) AND (PanelMensualF.cod152 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod501 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 501) AND (PanelMensualF.cod501 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod502 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 502) AND (PanelMensualF.cod502 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod510 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 510) AND (PanelMensualF.cod510 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod513 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 513) AND (PanelMensualF.cod513 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod529 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 529) AND (PanelMensualF.cod529 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod587 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 587) AND (PanelMensualF.cod587 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod709 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 709) AND (PanelMensualF.cod709 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod720 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 720) AND (PanelMensualF.cod720 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod732 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 732) AND (PanelMensualF.cod732 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET cod759 = CodOri_2018F.Valor
FROM PanelMensualF INNER JOIN CodOri_2018F ON PanelMensualF.Rut_ficticio = CodOri_2018F.Rut_ficticio AND PanelMensualF.Mes_dato = CodOri_2018F.Mes_dato AND
PanelMensualF.Orden = CodOri_2018F.Orden AND PanelMensualF.Folio = CodOri_2018F.Folio AND PanelMensualF.Mes_trib = CodOri_2018F.Mes_trib
WHERE (CodOri_2018F.Codigo = 759) AND (PanelMensualF.cod759 IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE [PanelMensualF]
SET Venta_mensual = ISNULL(cod20,0) + ISNULL(cod142,0) + ISNULL(cod587,0) + ISNULL(cod720,0) + ISNULL(cod732,0) + (((ISNULL(cod108,0) +ISNULL(cod502,0) + ISNULL(cod111,0) + ISNULL(cod112,0) +ISNULL(cod513,0) - ISNULL(cod510,0) - ISNULL(cod709,0) +ISNULL(cod759,0))+ ISNULL(cod501,0))/0.19) +(ISNULL(cod152,0) * 10) + ISNULL(cod529,0)
")
sqlQuery(channel = myconn,query = "
UPDATE [PanelMensualF]
SET Suma_codigos = ISNULL(cod20,0) + ISNULL(cod142,0) + ISNULL(cod587,0) + ISNULL(cod720,0) + ISNULL(cod732,0) + ISNULL(cod108,0) + ISNULL(cod502,0) + ISNULL(cod111,0) + ISNULL(cod112,0) + ISNULL(cod513,0) + ISNULL(cod510,0) + ISNULL(cod709,0) + ISNULL(cod501,0) + ISNULL(cod152,0) + ISNULL(cod529,0) + ISNULL(cod759,0)
")
sqlQuery(channel = myconn,query = "
SELECT Rut_ficticio, Mes_trib
INTO RutF_MesTF
FROM PanelMensualF
GROUP BY Rut_ficticio, Mes_trib
")
sqlQuery(channel = myconn,query = "
ALTER TABLE RutF_MesTF ADD Original int;
ALTER TABLE RutF_MesTF ADD Rectificatoria int;
ALTER TABLE RutF_MesTF ADD Total_Decl int;
ALTER TABLE RutF_MesTF ADD Caso_mensual nvarchar (10);
")
sqlQuery(channel = myconn,query = "
SELECT Origen, Rut_ficticio, Mes_trib, COUNT(Rut_ficticio) AS Expr1
INTO CuentaOrigF
FROM PanelMensualF
GROUP BY Rut_ficticio, Mes_trib, Origen
HAVING (Origen = N'ORI')
")
sqlQuery(channel = myconn,query = "
SELECT Origen, Rut_ficticio, Mes_trib, COUNT(Rut_ficticio) AS Expr1
INTO CuentaRectF
FROM PanelMensualF
GROUP BY Rut_ficticio, Mes_trib, Origen
HAVING (Origen = N'REC')
")
sqlQuery(channel = myconn,query = "
UPDATE RutF_MesTF
SET Original = CuentaOrigF.Expr1
FROM CuentaOrigF INNER JOIN RutF_MesTF ON CuentaOrigF.Rut_ficticio = RutF_MesTF.Rut_ficticio AND CuentaOrigF.Mes_trib = RutF_MesTF.Mes_trib
")
sqlQuery(channel = myconn,query = "
UPDATE RutF_MesTF
SET Rectificatoria = CuentaRectF.Expr1
FROM CuentaRectF INNER JOIN RutF_MesTF ON CuentaRectF.Rut_ficticio = RutF_MesTF.Rut_ficticio AND CuentaRectF.Mes_trib = RutF_MesTF.Mes_trib
")
sqlQuery(channel = myconn,query = "
UPDATE RutF_MesTF SET Total_Decl = ISNULL(Original,0) + ISNULL(Rectificatoria,0)
")
sqlQuery(channel = myconn,query = "
UPDATE RutF_MesTF SET Caso_mensual = N'CASO1' WHERE (Original = 1) AND (Rectificatoria IS NULL) AND (Caso_mensual IS NULL)
UPDATE RutF_MesTF SET Caso_mensual = N'CASO2' WHERE (Original IS NULL) AND (Rectificatoria = 1) AND (Caso_mensual IS NULL)
UPDATE RutF_MesTF SET Caso_mensual = N'CASO3' WHERE (Original = 1) AND (Rectificatoria = 1) AND (Caso_mensual IS NULL)
UPDATE RutF_MesTF SET Caso_mensual = N'CASO4' WHERE (Original > 1) AND (Rectificatoria IS NULL) AND (Caso_mensual IS NULL)
UPDATE RutF_MesTF SET Caso_mensual = N'CASO5' WHERE (Original > 1) AND (Rectificatoria = 1) AND (Caso_mensual IS NULL)
UPDATE RutF_MesTF SET Caso_mensual = N'CASO6' WHERE (Original > 1) AND (Rectificatoria > 1) AND (Caso_mensual IS NULL)
UPDATE RutF_MesTF SET Caso_mensual = N'CASO7' WHERE (Original = 1) AND (Rectificatoria > 1) AND (Caso_mensual IS NULL)
UPDATE RutF_MesTF SET Caso_mensual = N'CASO8' WHERE (Original IS NULL) AND (Rectificatoria > 1) AND (Caso_mensual IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET Caso_mensual = RutF_MesTF.Caso_mensual
FROM PanelMensualF INNER JOIN RutF_MesTF ON PanelMensualF.Rut_ficticio = RutF_MesTF.Rut_ficticio AND PanelMensualF.Mes_trib = RutF_MesTF.Mes_trib
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF SET Seleccion = N'SI', Criterio = N'Declaracion unica' WHERE (Caso_mensual = N'CASO1')
UPDATE PanelMensualF SET Seleccion = N'SI', Criterio = N'Declaracion unica' WHERE (Caso_mensual = N'CASO2')
UPDATE PanelMensualF SET Seleccion = N'SI', Criterio = N'Rectificatoria reemplaza original' WHERE (Origen = N'REC') AND (Caso_mensual = N'CASO3')
UPDATE PanelMensualF SET Seleccion = N'NO', Criterio = N'No seleccionado' WHERE (Origen = N'ORI') AND (Caso_mensual = N'CASO3')
")
sqlQuery(channel = myconn,query = "
SELECT 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, Seleccion, Criterio, Caso_mensual, Venta_mensual, Suma_codigos
INTO DepuracionF
FROM PanelMensualF
WHERE (Seleccion IS NULL) AND (Criterio IS NULL)
")
sqlQuery(channel = myconn,query = "
SELECT Rut_ficticio, Mes_trib, MAX(FechaPresentacion) AS Expr1
INTO FMax
FROM DepuracionF
GROUP BY Rut_ficticio, Mes_trib
")
sqlQuery(channel = myconn,query = "
SELECT DepuracionF.Rut_ficticio, DepuracionF.Mes_trib, DepuracionF.FechaPresentacion
INTO FMax_Unos
FROM DepuracionF INNER JOIN FMax ON DepuracionF.Rut_ficticio = FMax.Rut_ficticio AND DepuracionF.Mes_trib = FMax.Mes_trib AND DepuracionF.FechaPresentacion = FMax.Expr1
GROUP BY DepuracionF.Rut_ficticio, DepuracionF.Mes_trib, DepuracionF.FechaPresentacion
HAVING (COUNT(DepuracionF.Rut_ficticio) = 1)
")
sqlQuery(channel = myconn,query = "
UPDATE DepuracionF SET Seleccion = N'SI', Criterio = N'MáxFechaPresentación'
FROM DepuracionF INNER JOIN FMax_Unos ON DepuracionF.Rut_ficticio = FMax_Unos.Rut_ficticio AND DepuracionF.Mes_trib = FMax_Unos.Mes_trib AND
DepuracionF.FechaPresentacion = FMax_Unos.FechaPresentacion
")
sqlQuery(channel = myconn,query = "
UPDATE DepuracionF SET Seleccion = N'NO', Criterio = N'No seleccionado'
FROM DepuracionF INNER JOIN FMax_Unos ON DepuracionF.Rut_ficticio = FMax_Unos.Rut_ficticio AND DepuracionF.Mes_trib = FMax_Unos.Mes_trib
WHERE (DepuracionF.Seleccion IS NULL) AND (DepuracionF.Criterio IS NULL)
")
sqlQuery(channel = myconn,query = "
DROP TABLE FMax, FMax_Unos
")
sqlQuery(channel = myconn,query = "
SELECT DepuracionF.*, Seleccion AS Expr1, Criterio AS Expr2 INTO Pendientes
FROM DepuracionF WHERE (Seleccion IS NULL) AND (Criterio IS NULL)
")
sqlQuery(channel = myconn,query = "
SELECT Rut_ficticio, Mes_trib, MAX(FechaPresentacion) AS Expr1 INTO FMax
FROM Pendientes GROUP BY Rut_ficticio, Mes_trib
")
sqlQuery(channel = myconn,query = "
SELECT Pendientes.Rut_ficticio, Pendientes.Mes_trib, Pendientes.FechaPresentacion, MAX(Pendientes.Suma_codigos) AS Expr1
INTO CodMax
FROM Pendientes INNER JOIN FMax ON Pendientes.Rut_ficticio = FMax.Rut_ficticio AND Pendientes.Mes_trib = FMax.Mes_trib AND Pendientes.FechaPresentacion = FMax.Expr1
GROUP BY Pendientes.Rut_ficticio, Pendientes.Mes_trib, Pendientes.FechaPresentacion
")
sqlQuery(channel = myconn,query = "
SELECT Pendientes.Rut_ficticio, Pendientes.Mes_trib, Pendientes.FechaPresentacion, Pendientes.Suma_codigos, COUNT(Pendientes.Rut_ficticio) AS Expr1
INTO FMaxUnos
FROM Pendientes INNER JOIN CodMax ON Pendientes.Rut_ficticio = CodMax.Rut_ficticio AND Pendientes.Mes_trib = CodMax.Mes_trib AND
Pendientes.FechaPresentacion = CodMax.FechaPresentacion AND Pendientes.Suma_codigos = CodMax.Expr1
GROUP BY Pendientes.Rut_ficticio, Pendientes.Mes_trib, Pendientes.FechaPresentacion, Pendientes.Suma_codigos
HAVING (COUNT(Pendientes.Rut_ficticio) = 1)
")
sqlQuery(channel = myconn,query = "
UPDATE DepuracionF SET Seleccion = N'SI', Criterio = N'MáxFechaPresentaciónPrepetida_DeclaraDiferentes_MaxCod'
FROM DepuracionF INNER JOIN FMaxUnos ON DepuracionF.Rut_ficticio = FMaxUnos.Rut_ficticio AND DepuracionF.Mes_trib = FMaxUnos.Mes_trib AND
DepuracionF.FechaPresentacion = FMaxUnos.FechaPresentacion AND DepuracionF.Suma_codigos = FMaxUnos.Suma_codigos
WHERE (DepuracionF.Origen = N'ORI') AND (DepuracionF.Seleccion IS NULL) AND (DepuracionF.Criterio IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE DepuracionF
SET Seleccion = N'SI', Criterio = N'MáxFechaPresentaciónRrepetida_DeclaraDiferentes_MaxCod'
FROM DepuracionF INNER JOIN
FMaxUnos ON DepuracionF.Rut_ficticio = FMaxUnos.Rut_ficticio AND DepuracionF.Mes_trib = FMaxUnos.Mes_trib AND
DepuracionF.FechaPresentacion = FMaxUnos.FechaPresentacion AND DepuracionF.Suma_codigos = FMaxUnos.Suma_codigos
WHERE (DepuracionF.Origen = N'REC') AND (DepuracionF.Seleccion IS NULL) AND (DepuracionF.Criterio IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE DepuracionF
SET Criterio = N'No seleccionado', Seleccion = N'NO'
FROM DepuracionF INNER JOIN
FMaxUnos ON DepuracionF.Rut_ficticio = FMaxUnos.Rut_ficticio AND DepuracionF.Mes_trib = FMaxUnos.Mes_trib
WHERE (DepuracionF.Criterio IS NULL) AND (DepuracionF.Seleccion IS NULL)
")
sqlQuery(channel = myconn,query = "
DROP TABLE FMax, FMax_Unos, CodMax, Pendientes
")
sqlQuery(channel = myconn,query = "
SELECT DepuracionF.*, Seleccion AS Expr1, Criterio AS Expr2
INTO Pendientes
FROM DepuracionF
WHERE (Seleccion IS NULL) AND (Criterio IS NULL)
")
sqlQuery(channel = myconn,query = "
SELECT Rut_ficticio, Mes_trib, MAX(FechaPresentacion) AS Expr1
INTO FMax
FROM Pendientes
GROUP BY Rut_ficticio, Mes_trib
")
sqlQuery(channel = myconn,query = "
SELECT Pendientes.Rut_ficticio, Pendientes.Mes_trib, Pendientes.FechaPresentacion, MAX(Pendientes.Suma_codigos) AS Expr1
INTO CodMax
FROM Pendientes INNER JOIN
FMax ON Pendientes.Rut_ficticio = FMax.Rut_ficticio AND Pendientes.Mes_trib = FMax.Mes_trib AND Pendientes.FechaPresentacion = FMax.Expr1
GROUP BY Pendientes.Rut_ficticio, Pendientes.Mes_trib, Pendientes.FechaPresentacion
")
sqlQuery(channel = myconn,query = "
SELECT Pendientes.Rut_ficticio, Pendientes.Mes_trib, Pendientes.FechaPresentacion, Pendientes.Suma_codigos, MAX(Pendientes.Orden) AS MaxOrden
INTO OrdenMax
FROM Pendientes INNER JOIN
CodMax AS CodMax_1 ON Pendientes.Rut_ficticio = CodMax_1.Rut_ficticio AND Pendientes.Mes_trib = CodMax_1.Mes_trib AND
Pendientes.FechaPresentacion = CodMax_1.FechaPresentacion AND Pendientes.Suma_codigos = CodMax_1.Expr1
GROUP BY Pendientes.Rut_ficticio, Pendientes.Mes_trib, Pendientes.FechaPresentacion, Pendientes.Suma_codigos
")
sqlQuery(channel = myconn,query = "
SELECT Pendientes.Rut_ficticio, Pendientes.Mes_trib, Pendientes.FechaPresentacion, Pendientes.Suma_codigos, OrdenMax.MaxOrden
INTO Unos
FROM Pendientes INNER JOIN
OrdenMax ON Pendientes.Rut_ficticio = OrdenMax.Rut_ficticio AND Pendientes.Mes_trib = OrdenMax.Mes_trib AND
Pendientes.FechaPresentacion = OrdenMax.FechaPresentacion AND Pendientes.Suma_codigos = OrdenMax.Suma_codigos AND
Pendientes.Orden = OrdenMax.MaxOrden
GROUP BY Pendientes.Rut_ficticio, Pendientes.Mes_trib, Pendientes.FechaPresentacion, Pendientes.Suma_codigos, OrdenMax.MaxOrden
HAVING (COUNT(Pendientes.Rut_ficticio) = 1)
")
sqlQuery(channel = myconn,query = "
UPDATE DepuracionF
SET Seleccion = N'SI', Criterio = N'MáxFechaPresentaciónPrepetida_DeclaraIgual_MaxOrden'
FROM DepuracionF INNER JOIN
Unos ON DepuracionF.Rut_ficticio = Unos.Rut_ficticio AND DepuracionF.Mes_trib = Unos.Mes_trib AND DepuracionF.FechaPresentacion = Unos.FechaPresentacion AND
DepuracionF.Suma_codigos = Unos.Suma_codigos AND DepuracionF.Orden = Unos.MaxOrden
WHERE (DepuracionF.Origen = N'ORI') AND (DepuracionF.Seleccion IS NULL) AND (DepuracionF.Criterio IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE DepuracionF
SET Seleccion = N'SI', Criterio = N'MáxFechaPresentaciónRrepetida_DeclaraIgual_MaxOrden'
FROM DepuracionF INNER JOIN
Unos ON DepuracionF.Rut_ficticio = Unos.Rut_ficticio AND DepuracionF.Mes_trib = Unos.Mes_trib AND DepuracionF.FechaPresentacion = Unos.FechaPresentacion AND
DepuracionF.Suma_codigos = Unos.Suma_codigos AND DepuracionF.Orden = Unos.MaxOrden
WHERE (DepuracionF.Origen = N'REC') AND (DepuracionF.Seleccion IS NULL) AND (DepuracionF.Criterio IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE DepuracionF
SET Criterio = N'No seleccionado', Seleccion = N'NO'
FROM DepuracionF INNER JOIN
Unos ON DepuracionF.Rut_ficticio = Unos.Rut_ficticio AND DepuracionF.Mes_trib = Unos.Mes_trib
WHERE (DepuracionF.Criterio IS NULL) AND (DepuracionF.Seleccion IS NULL)
")
sqlQuery(channel = myconn,query = "
DROP TABLE FMax, FMaxUnos, CodMax, OrdenMax, Unos, Pendientes
")
sqlQuery(channel = myconn,query = "
UPDATE PanelMensualF
SET Seleccion = DepuracionF.Seleccion, Criterio = DepuracionF.Criterio
FROM DepuracionF INNER JOIN
PanelMensualF ON DepuracionF.Rut_ficticio = PanelMensualF.Rut_ficticio AND DepuracionF.Mes_trib = PanelMensualF.Mes_trib AND
DepuracionF.Mes_dato = PanelMensualF.Mes_dato AND DepuracionF.Orden = PanelMensualF.Orden
WHERE (PanelMensualF.Seleccion IS NULL) AND (PanelMensualF.Criterio IS NULL)
")
sqlQuery(channel = myconn,query = "
SELECT Seleccion, Rut_ficticio
INTO VentasMesF
FROM PanelMensualF
GROUP BY Seleccion, Rut_ficticio
HAVING (Seleccion = N'SI')
")
sqlQuery(channel = myconn,query = "
ALTER TABLE VentasMesF ADD ENE float;
ALTER TABLE VentasMesF ADD FEB float;
ALTER TABLE VentasMesF ADD MAR float;
ALTER TABLE VentasMesF ADD ABR float;
ALTER TABLE VentasMesF ADD MAY float;
ALTER TABLE VentasMesF ADD JUN float;
ALTER TABLE VentasMesF ADD JUL float;
ALTER TABLE VentasMesF ADD AGO float;
ALTER TABLE VentasMesF ADD SEP float;
ALTER TABLE VentasMesF ADD OCT float;
ALTER TABLE VentasMesF ADD NOV float;
ALTER TABLE VentasMesF ADD DIC float;
ALTER TABLE VentasMesF ADD Venta_anual float;
")
sqlQuery(channel = myconn,query = "
UPDATE VentasMesF
SET ENE = PanelMensualF.Venta_mensual
FROM VentasMesF INNER JOIN
PanelMensualF ON VentasMesF.Rut_ficticio = PanelMensualF.Rut_ficticio
WHERE (PanelMensualF.Mes_trib = 1) AND (PanelMensualF.Seleccion = N'SI') AND (VentasMesF.ENE IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE VentasMesF
SET FEB = PanelMensualF.Venta_mensual
FROM VentasMesF INNER JOIN
PanelMensualF ON VentasMesF.Rut_ficticio = PanelMensualF.Rut_ficticio
WHERE (PanelMensualF.Mes_trib = 2) AND (PanelMensualF.Seleccion = N'SI') AND (VentasMesF.FEB IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE VentasMesF
SET MAR = PanelMensualF.Venta_mensual
FROM VentasMesF INNER JOIN
PanelMensualF ON VentasMesF.Rut_ficticio = PanelMensualF.Rut_ficticio
WHERE (PanelMensualF.Mes_trib = 3) AND (PanelMensualF.Seleccion = N'SI') AND (VentasMesF.MAR IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE VentasMesF
SET ABR = PanelMensualF.Venta_mensual
FROM VentasMesF INNER JOIN
PanelMensualF ON VentasMesF.Rut_ficticio = PanelMensualF.Rut_ficticio
WHERE (PanelMensualF.Mes_trib = 4) AND (PanelMensualF.Seleccion = N'SI') AND (VentasMesF.ABR IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE VentasMesF
SET MAY = PanelMensualF.Venta_mensual
FROM VentasMesF INNER JOIN
PanelMensualF ON VentasMesF.Rut_ficticio = PanelMensualF.Rut_ficticio
WHERE (PanelMensualF.Mes_trib = 5) AND (PanelMensualF.Seleccion = N'SI') AND (VentasMesF.MAY IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE VentasMesF
SET JUN = PanelMensualF.Venta_mensual
FROM VentasMesF INNER JOIN
PanelMensualF ON VentasMesF.Rut_ficticio = PanelMensualF.Rut_ficticio
WHERE (PanelMensualF.Mes_trib = 6) AND (PanelMensualF.Seleccion = N'SI') AND (VentasMesF.JUN IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE VentasMesF
SET JUL = PanelMensualF.Venta_mensual
FROM VentasMesF INNER JOIN
PanelMensualF ON VentasMesF.Rut_ficticio = PanelMensualF.Rut_ficticio
WHERE (PanelMensualF.Mes_trib = 7) AND (PanelMensualF.Seleccion = N'SI') AND (VentasMesF.JUL IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE VentasMesF
SET AGO = PanelMensualF.Venta_mensual
FROM VentasMesF INNER JOIN
PanelMensualF ON VentasMesF.Rut_ficticio = PanelMensualF.Rut_ficticio
WHERE (PanelMensualF.Mes_trib = 8) AND (PanelMensualF.Seleccion = N'SI') AND (VentasMesF.AGO IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE VentasMesF
SET SEP = PanelMensualF.Venta_mensual
FROM VentasMesF INNER JOIN
PanelMensualF ON VentasMesF.Rut_ficticio = PanelMensualF.Rut_ficticio
WHERE (PanelMensualF.Mes_trib = 9) AND (PanelMensualF.Seleccion = N'SI') AND (VentasMesF.SEP IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE VentasMesF
SET OCT = PanelMensualF.Venta_mensual
FROM VentasMesF INNER JOIN
PanelMensualF ON VentasMesF.Rut_ficticio = PanelMensualF.Rut_ficticio
WHERE (PanelMensualF.Mes_trib = 10) AND (PanelMensualF.Seleccion = N'SI') AND (VentasMesF.nov IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE VentasMesF
SET NOV = PanelMensualF.Venta_mensual
FROM VentasMesF INNER JOIN
PanelMensualF ON VentasMesF.Rut_ficticio = PanelMensualF.Rut_ficticio
WHERE (PanelMensualF.Mes_trib = 11) AND (PanelMensualF.Seleccion = N'SI') AND (VentasMesF.NOV IS NULL)
")
sqlQuery(channel = myconn,query = "
UPDATE VentasMesF
SET DIC = PanelMensualF.Venta_mensual
FROM VentasMesF INNER JOIN
PanelMensualF ON VentasMesF.Rut_ficticio = PanelMensualF.Rut_ficticio
WHERE (PanelMensualF.Mes_trib = 12) AND (PanelMensualF.Seleccion = N'SI') AND (VentasMesF.DIC IS NULL)
")
sqlQuery(channel = myconn,query = "
INSERT INTO VentasMesF
(Rut_ficticio)
SELECT TotalULF.Rut_ficticio
FROM TotalULF LEFT OUTER JOIN
VentasMesF AS VentasMesF_1 ON TotalULF.Rut_ficticio = VentasMesF_1.Rut_ficticio
WHERE (VentasMesF_1.Rut_ficticio IS NULL) AND (TotalULF.Observacion <> 'EJERCICIO DE PRUEBA' OR
TotalULF.Observacion IS NULL)
")
sqlQuery(channel = myconn,query = "
DROP TABLE oriF, recF, CodOri_2018F, CodRec_2018F, CuentaOrigF, CuentaRectF
")
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment