Paste code

DROP TABLE IF EXISTS ..............T_MERCADO_BPE_ADQ_SF_P01


				CREATE TABLE ..............T_MERCADO_BPE_ADQ_SF_P01
				WITH ( format = &#039;Parquet&#039;, 
					   parquet_compression = &#039;SNAPPY&#039;, 
					   partitioned_by = ARRAY[&#039;PERIODO&#039;], 
					   external_location= &#039;s3://................................................./T_MERCADO_BPE_ADQ_SF_P01/&#039;
					 )
				AS (
				SELECT 
					  cod_sbs_val AS CODSBS,
					  producto_rcc_desc AS PRODUCTO,
					  situacion_rcc_desc AS SITUACION,
					  empresa_cd AS COD_EMPRESA,
					  CASE WHEN coalesce(tipoempresafinanc_desc,&#039;&#039;) = &#039;BANCOS&#039; AND coalesce(estadoempresafinanc_desc,&#039;&#039;) =&#039;EN OPERACION&#039; THEN &#039;BANCOS&#039; ELSE &#039;CAJAS & FINANC.&#039; END AS TIPO_ENTIDAD,
					  CASE WHEN coalesce(tipoempresafinanc_desc,&#039;&#039;) = &#039;BANCOS&#039; AND coalesce(estadoempresafinanc_desc,&#039;&#039;) =&#039;EN OPERACION&#039; THEN nomcortoempresafinanc_desc ELSE &#039;CAJAS & FINANC.&#039; END AS BANCO,
					  tipo_producto_rcc_desc TIPO_PRODUCTO,
					  SUM(coalesce(saldo_amt, 0)) MTO_SALDO,
				      MAX(coalesce(flg_lin_mercado, &#039;N&#039;)) flg_lin_mercado,
					  MAX(coalesce(condicion_val, 0)) MAX_DIAS_ATRASO,
                      CASE WHEN cod_sbs_val in (select cod_sbs_val from ................t_vpc_segmentacion_merc where banca_fin_val = &#039;BPE&#039; or banca_mes_val = &#039;BPE&#039;) THEN 1 ELSE 0 END FLG_BPE_TIMELINE,
                      CASE WHEN cod_sbs_val in (select cod_sbs_val from ................t_vpc_segmentacion_merc where banca_fin_val = &#039;BE&#039; or banca_mes_val = &#039;BE&#039;) THEN 1 ELSE 0 END FLG_BE_TIMELINE,
                      CASE WHEN cod_sbs_val in (select cod_sbs_val from ................t_vpc_segmentacion_merc where banca_fin_val = &#039;BC&#039; or banca_mes_val = &#039;BC&#039;) THEN 1 ELSE 0 END FLG_BC_TIMELINE,
                      CASE WHEN cod_sbs_val in (select cod_sbs_val from ................t_vpc_segmentacion_merc where banca_fin_val = &#039;MICRO&#039; or banca_mes_val = &#039;MICRO&#039;) THEN 1 ELSE 0 END FLG_MICRO_TIMELINE,
					  periodo_val AS PERIODO
				   FROM ................T_VPC_FACT_SALDOS_RCC
				   WHERE 1 = 1
				   AND date_parse(cast(periodo_val as varchar),&#039;%Y%m&#039;) >= (SELECT date_add(&#039;month&#039;, -18, date_parse(cast(MAX(periodo_val) as varchar),&#039;%Y%m&#039;)) FROM ................T_VPC_FACT_SALDOS_RCC)
				   AND tipo_producto_rcc_desc IN (
					 &#039;COL. DIRECTAS&#039;,&#039;REACTIVA&#039;,&#039;COL. INDIRECTAS&#039;,&#039;FAE&#039;,&#039;INMOBILIARIO&#039;, &#039;CREDITOS CASTIGADOS&#039;, &#039;GARANTIA_FAE&#039;, &#039;GARANTIA_REACTIVA&#039;, &#039;GARANTIAS&#039;, &#039;REPROGRAMADOS&#039;
				   )
				   AND 
				   (
					  (
						  flg_lin_mercado = &#039;S&#039; AND
						  --(
							producto_rcc_desc NOT IN (&#039;OTROS&#039;) 
							--OR 
							--(
							--  B.PRODUCTO =&#039;OTROS&#039; AND 
							--  B.DETALLE IN (&#039;FINANCIACION DE PROYECTOS&#039;)
							--)
						  --)
					  )					
					  OR tipo_producto_rcc_desc IN (&#039;REACTIVA&#039;,&#039;FAE&#039;,&#039;INMOBILIARIO&#039;, &#039;CREDITOS CASTIGADOS&#039;, &#039;GARANTIA_FAE&#039;, &#039;GARANTIA_REACTIVA&#039;, &#039;GARANTIAS&#039;, &#039;REPROGRAMADOS&#039;)
				  )
				  GROUP BY 
					  periodo_val,
					  cod_sbs_val,
					  producto_rcc_desc,
					  situacion_rcc_desc,
					  empresa_cd,
					  CASE WHEN coalesce(tipoempresafinanc_desc,&#039;&#039;) =&#039;BANCOS&#039; AND coalesce(estadoempresafinanc_desc,&#039;&#039;) =&#039;EN OPERACION&#039; THEN &#039;BANCOS&#039; ELSE &#039;CAJAS & FINANC.&#039; END,
					  CASE WHEN coalesce(tipoempresafinanc_desc,&#039;&#039;) =&#039;BANCOS&#039; AND coalesce(estadoempresafinanc_desc,&#039;&#039;) =&#039;EN OPERACION&#039; THEN nomcortoempresafinanc_desc ELSE &#039;CAJAS & FINANC.&#039; END,
					  tipo_producto_rcc_desc
				  
				)