Psst.. new poll here.
Psst.. new forums here.
Microsoft is blocking us again (TY IP Reputation!) so dont bother with any of their useless mail servers here and just use oauth login instead. Thank the nice Russians for causing that. :)
Paste
Pasted as Plain Text by registered user vvillacorta ( 5 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