Paste code

SELECT A.FECCARGA,  TRIM(A.NUMRUC) NUMRUC, 
						                 CASE WHEN SUBSTR(NUMRUC,1,2) = &#039;10&#039; AND (TRIM(A.CODDOCRELE) LIKE &#039;&#039; OR A.CODDOCRELE IS NULL) THEN TRIM(SUBSTR(NUMRUC,3,8))
										 ELSE TRIM(A.CODDOCRELE) END CODDOCRELE,
					    MAX(CASE WHEN S.ORDER_CODSBS = 1 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_1,
						MAX(CASE WHEN S.ORDER_CODSBS = 2 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_2,
						MAX(CASE WHEN S.ORDER_CODSBS = 3 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_3,
						MAX(CASE WHEN S.ORDER_CODSBS = 4 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_4,
						MAX(CASE WHEN S.ORDER_CODSBS = 5 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_5,
						MAX(CASE WHEN S.ORDER_CODSBS = 6 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_6,
					    MAX(CASE WHEN R.RRLL_CODSBS = 1 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_1,
						MAX(CASE WHEN R.RRLL_CODSBS = 2 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_2,
						MAX(CASE WHEN R.RRLL_CODSBS = 3 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_3,
						MAX(CASE WHEN R.RRLL_CODSBS = 4 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_4,
						MAX(CASE WHEN R.RRLL_CODSBS = 5 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_5,
						MAX(CASE WHEN R.RRLL_CODSBS = 6 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_6,
						MAX(S.CODUNICOCLI) COD_UNICO,
			            MAX(RAZONSOCIAL)  RAZONSOCIAL,
						MAX(TIPCONTRIBUYENTE) TIPCONTRIBUYENTE, 
						MAX(TIPPERSONA) TIPPERSONA, 
						MAX(CIIU) CIIU,
						MAX(UBIGEO)  UBIGEO,
						CASE WHEN MAX(NUMTRABAJADORES) LIKE &#039;&#039; THEN 0 
						            WHEN MAX(NUMTRABAJADORES) LIKE &#039;NE&#039; THEN 0
									ELSE CAST(MAX(NUMTRABAJADORES) AS INTEGER)
						 END NUMTRABAJADORES,
							 CASE WHEN MAX(RANGOVENTAS) LIKE &#039;&#039;  OR MAX(RANGOVENTAS) IS NULL THEN 0 ELSE MAX(RANGOVENTAS) END RANGOVENTAS,
						MAX(CONDICIONDOMICILIO) CONDICIONDOMICILIO,
						MAX(ESTADOCONTRIBUYENTE) ESTADOCONTRIBUYENTE,
						
						MAX(FECALTA) FECALTA,
						CASE WHEN MAX(FECALTA) IS NULL THEN NULL
						           ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT &#039;YYYYMM&#039;),CAST(MAX(FECALTA) AS DATE FORMAT &#039;YYYYMM&#039;)) / 12 END TIEMPO_ALTA,
						MAX(FECBAJA) FECBAJA,
						CASE WHEN MAX(FECBAJA) IS NULL THEN 0
						          ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT &#039;YYYYMM&#039;),CAST(MAX(FECBAJA) AS DATE FORMAT &#039;YYYYMM&#039;)) / 12 END TIEMPO_BAJA,
						MAX(CONTABILIDAD) CONTABILIDAD,
						MAX(FACTURACION) FACTURACION,
						MAX(COMERCIOEXTERIOR) COMERCIOEXTERIOR,
						MAX(ESTADO) ESTADO,
						MAX(CASE WHEN TIENEDIR = &#039;SI&#039; THEN 1 ELSE 0 END) FLG_TIENEDIR,
						MAX(CASE WHEN TIENETELEF = &#039;SI&#039; THEN 1 ELSE 0 END) FLG_TIENETELEF,
						
						MAX(ESTADORELE) ESTADORELE,
						MAX(CASE  WHEN SUBSTR(NUMRUC,1,2) = &#039;10&#039; AND (TRIM(A.CODDOCRELE) LIKE &#039;&#039; OR A.CODDOCRELE IS NULL) THEN FECALTA ELSE FECVIGENCIARELE END) FECVIGENCIARELE,
						
						MAX(CASE WHEN SUBSTR(NUMRUC,1,2) = &#039;10&#039; AND (TRIM(A.CODDOCRELE) LIKE &#039;&#039; OR A.CODDOCRELE IS NULL) THEN (
						                CASE WHEN FECALTA IS NULL THEN NULL
						                           ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT &#039;YYYYMM&#039;),CAST(FECALTA AS DATE FORMAT &#039;YYYYMM&#039;)) / 12
									    END
						           )
						           WHEN FECVIGENCIARELE IS NULL THEN NULL
						           ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT &#039;YYYYMM&#039;),CAST(FECVIGENCIARELE AS DATE FORMAT &#039;YYYYMM&#039;)) / 12
						END) TIEMPO_VIGENCIARELE,
						
						MAX(B.EST_CIVIL) EST_CIVIL,
						MAX(B.FEC_NACIMIENTO)  FECHA_NACIMIENTO,
						MAX(CASE WHEN B.FEC_NACIMIENTO IS NULL THEN NULL
						           ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT &#039;YYYYMM&#039;),CAST(B.FEC_NACIMIENTO AS DATE FORMAT &#039;YYYYMM&#039;)) / 12
						END) EDAD_RRLL,

		                  MAX(CASE WHEN EST_CIVIL = &#039;D&#039; THEN 1 ELSE 0 END) EST_CIVIL_DIVORCIADO,
						  MAX(CASE WHEN EST_CIVIL = &#039;S&#039; THEN 1 ELSE 0 END) EST_CIVIL_SOLTERO,
						  MAX(CASE WHEN EST_CIVIL =&#039;C&#039; THEN 1 ELSE 0 END) EST_CIVIL_CASADO,
						  MAX(CASE WHEN EST_CIVIL = &#039;V&#039; THEN 1 ELSE 0 END) EST_CIVIL_VIUDO,
		                  MAX(GENERO)  GENERO,
						  MAX(CASE WHEN GENERO = &#039;F&#039; THEN 1 ELSE 0 END) EST_CIVIL_FEMENINO,
						  MAX(CASE WHEN GENERO = &#039;M&#039; THEN 1 ELSE 0 END) EST_CIVIL_MASCULINO,
						  MAX(CASE WHEN DON_ORGANOS = &#039;SI&#039; THEN 1 ELSE 0 END) DON_ORGANOS,
						  MAX(CASE WHEN DEP_UBI_DOMICILIO IN ( &#039;LIMA&#039;  , &#039;CALLAO&#039;  ) THEN &#039;LIMA y CALLAO&#039;        
						              WHEN DEP_UBI_DOMICILIO IN ( &#039;AMAZONAS&#039;, &#039;ANCASH&#039; ,&#039;APURIMAC&#039; , &#039;AREQUIPA&#039;, &#039;AYACUCHO&#039; , &#039;CAJAMARCA&#039;, &#039;CUSCO&#039;,  &#039;HUANCAVELICA&#039; , &#039;HUANUCO&#039; , &#039;ICA&#039;,&#039;JUNIN&#039;, 
									                                                            &#039;LA LIBERTAD&#039; , &#039;LAMBAYEQUE&#039;, &#039;LORETO&#039; , &#039;MADRE DE DIOS&#039;, &#039;MOQUEGUA&#039;, &#039;PASCO&#039; , &#039;PIURA&#039;, &#039;PUNO&#039;, &#039;SAN MARTIN&#039;, &#039;TACNA&#039;, &#039;TUMBES&#039; , &#039;UCAYALI&#039;)  THEN &#039;PROVINCIA&#039; 
																								ELSE &#039;EXTRANJERO&#039; END) LUGAR_RESIDENCIA,
						  MAX(CASE WHEN DEP_UBI_DOMICILIO IN ( &#039;LIMA&#039;  , &#039;CALLAO&#039;  ) THEN 1 ELSE 0 END) RESIDENCIA_LIMA_CALLAO,
						  MAX(CASE WHEN DEP_UBI_DOMICILIO IN ( &#039;AMAZONAS&#039;, &#039;ANCASH&#039; ,&#039;APURIMAC&#039; , &#039;AREQUIPA&#039;, &#039;AYACUCHO&#039; , &#039;CAJAMARCA&#039;, &#039;CUSCO&#039;,  &#039;HUANCAVELICA&#039; , &#039;HUANUCO&#039; , &#039;ICA&#039;,&#039;JUNIN&#039;, 
									                                                            &#039;LA LIBERTAD&#039; , &#039;LAMBAYEQUE&#039;, &#039;LORETO&#039; , &#039;MADRE DE DIOS&#039;, &#039;MOQUEGUA&#039;, &#039;PASCO&#039; , &#039;PIURA&#039;, &#039;PUNO&#039;, &#039;SAN MARTIN&#039;, &#039;TACNA&#039;, &#039;TUMBES&#039; , &#039;UCAYALI&#039;)  THEN 1 ELSE 0 END) RESIDENCIA_PROVICNCIA,
						   MAX(CASE WHEN DEP_UBI_DOMICILIO NOT IN (&#039;LIMA&#039;  , &#039;CALLAO&#039;, &#039;AMAZONAS&#039;, &#039;ANCASH&#039; ,&#039;APURIMAC&#039; , &#039;AREQUIPA&#039;, &#039;AYACUCHO&#039; , &#039;CAJAMARCA&#039;, &#039;CUSCO&#039;,  &#039;HUANCAVELICA&#039; , &#039;HUANUCO&#039; , &#039;ICA&#039;,&#039;JUNIN&#039;, 
									                                                            &#039;LA LIBERTAD&#039; , &#039;LAMBAYEQUE&#039;, &#039;LORETO&#039; , &#039;MADRE DE DIOS&#039;, &#039;MOQUEGUA&#039;, &#039;PASCO&#039; , &#039;PIURA&#039;, &#039;PUNO&#039;, &#039;SAN MARTIN&#039;, &#039;TACNA&#039;, &#039;TUMBES&#039; , &#039;UCAYALI&#039;)  THEN 1 ELSE 0 END) RESIDENCIA_EXTRANJERA,

							
                          &#039;202109&#039; PERIODO_VAL -- MES PARAMETRO (mes actual - 1 , a que se debe correr el 1ero de cada mes)
					   FROM E_DW_VIEWS.V_CONSOLIDADO_SUNAT_HIST A
						LEFT JOIN  E_DW_VIEWS.V_MAESTRA_RENIEC  B  
						ON ( CASE WHEN A.NUMRUC LIKE &#039;10%&#039;   THEN A.CODDOC ELSE A.CODDOCRELE END) = B.CODDOC
						LEFT JOIN (
						       SELECT ROW_NUMBER() OVER (PARTITION BY 
                                                 (CASE WHEN NUMDOCTRIB LIKE &#039;.&#039; OR  NUMDOCTRIB LIKE &#039;&#039; OR NUMDOCTRIB IS NULL THEN NUMDOCTRIB_TRDT ELSE NUMDOCTRIB END)
                                                 ORDER BY NUMDOCTRIB DESC, TIPPERSONA ASC, CODSBS) ORDER_CODSBS,
                               CODSBS,  NUMDOCTRIB_TRDT, NUMDOCTRIB, CODDOC_TRDT, CODDOC, CODUNICOCLI
                               FROM E_DW_VIEWS.V_RSK_FCT_MAEPERSONA_RCC
						) S
						ON A.NUMRUC = (CASE WHEN S.NUMDOCTRIB LIKE &#039;.&#039; OR  S.NUMDOCTRIB LIKE &#039;&#039; OR S.NUMDOCTRIB IS NULL THEN S.NUMDOCTRIB_TRDT ELSE S.NUMDOCTRIB END)
						LEFT JOIN(
						       SELECT ROW_NUMBER() OVER (PARTITION BY 
                                                 (CASE WHEN CODDOC LIKE &#039;.&#039; OR  CODDOC LIKE &#039;&#039; OR CODDOC IS NULL THEN CODDOC_TRDT ELSE CODDOC END)
                                                 ORDER BY NUMDOCTRIB DESC, TIPPERSONA ASC, CODSBS) RRLL_CODSBS,
                                CODSBS,  NUMDOCTRIB_TRDT, NUMDOCTRIB, CODDOC_TRDT, CODDOC, CODUNICOCLI
                                FROM E_DW_VIEWS.V_RSK_FCT_MAEPERSONA_RCC
                                WHERE CODDOC NOT LIKE &#039;.&#039; OR CODDOC_TRDT NOT LIKE &#039;.&#039; 
						) R
						ON A.CODDOCRELE =  (CASE WHEN R.CODDOC LIKE &#039;.&#039; OR  R.CODDOC LIKE &#039;&#039; OR R.CODDOC IS NULL THEN R.CODDOC_TRDT ELSE R.CODDOC END)

					    WHERE 1 = 1
						AND ESTADORELE <> 9
						AND SUBSTR(NUMRUC,1,2) IN (&#039;15&#039;,&#039;17&#039;,&#039;20&#039;,&#039;10&#039;)
						AND A.FECCARGA = &#039;202107&#039; --&#039;PERIODO_A_COPIAR DE SUNAT HIST&#039;
                      GROUP BY   A.FECCARGA,  TRIM(A.NUMRUC), 
					                        CASE WHEN SUBSTR(NUMRUC,1,2) = &#039;10&#039; AND (TRIM(A.CODDOCRELE) LIKE &#039;&#039; OR A.CODDOCRELE IS NULL) THEN TRIM(SUBSTR(NUMRUC,3,8))
										    ELSE TRIM(A.CODDOCRELE) END