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