DROP TABLE IF EXISTS ..........................T_MERCADO_BPE_ADQ_SF_P03_AUTOCOMPLETADO_U9M


CREATE TABLE ..........................T_MERCADO_BPE_ADQ_SF_P03_AUTOCOMPLETADO_U9M
WITH ( format = 'Parquet', 
       parquet_compression = 'SNAPPY', 
       partitioned_by = ARRAY['PERIODO'], 
       external_location= 's3://..................................................................................................../T_MERCADO_BPE_ADQ_SF_P03_AUTOCOMPLETADO_U9M/'
)
AS (

SELECT DISTINCT A.CODSBS, A.COD_EMPRESA, A.PERIODO
FROM (
  
  SELECT substr(replace(cast(cast(date_parse(cast(A.PERIODO as varchar),'%Y%m') as date) as varchar), '-', ''), 1, 6) PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A 
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', +1, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', +2, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A 
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', +3, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', +4, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', +5, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', +6, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', +7, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', +8, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', +9, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', -1, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', -2, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', -3, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', -4, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', -5, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', -6, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', -7, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', -8, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM ..........................T_MERCADO_BPE_ADQ_SF_P03 A
  UNION ALL
  SELECT substr(replace(cast(cast(date_add('month', -9, date_parse(cast(A.PERIODO as varchar),'%Y%m')) as date) as varchar), '-', ''), 1, 6)  PERIODO, A.CODSBS, A.COD_EMPRESA FROM .........................T_MERCADO_BPE_ADQ_SF_P03 A

 ) A
 WHERE 1 = 1
 AND date_parse(cast(A.PERIODO as varchar),&#039;%Y%m&#039;) <= (SELECT date_parse(cast(MAX(PERIODO) as varchar),&#039;%Y%m&#039;) FROM ..........T_MERCADO_BPE_ADQ_SF_P03)
 AND date_parse(cast(A.PERIODO as varchar),&#039;%Y%m&#039;) >= (SELECT date_parse(cast(MIN(PERIODO) as varchar),&#039;%Y%m&#039;) FROM ............T_MERCADO_BPE_ADQ_SF_P03)



)

Add a code snippet to your website: www.paste.org