Welcome, guest! Login / Register - Why register?
Psst.. new poll here.
[email protected] web/email now available. Want one? Go here.
Cannot use outlook/hotmail/live here to register as they blocking our mail servers. #microsoftdeez
Obey the Epel!

Paste

Pasted as Plain Text by registered user vvillacorta ( 3 years ago )
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),'%Y%m') <= (SELECT date_parse(cast(MAX(PERIODO) as varchar),'%Y%m') FROM ..........T_MERCADO_BPE_ADQ_SF_P03)
 AND date_parse(cast(A.PERIODO as varchar),'%Y%m') >= (SELECT date_parse(cast(MIN(PERIODO) as varchar),'%Y%m') FROM ............T_MERCADO_BPE_ADQ_SF_P03)



)

 

Revise this Paste

Your Name: Code Language: