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 .................t3_mercado_bpe_prestamo_tarjeta_autocompletado
CREATE TABLE .................t3_mercado_bpe_prestamo_tarjeta_autocompletado
WITH ( format = 'Parquet',
parquet_compression = 'SNAPPY',
partitioned_by = ARRAY['PERIODO'],
external_location= 's3://........................................../t3_mercado_bpe_prestamo_tarjeta_autocompletado/'
)
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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta 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 .................t3_mercado_bpe_prestamo_tarjeta)
AND date_parse(cast(A.PERIODO as varchar),'%Y%m') >= (SELECT date_parse(cast(MIN(PERIODO) as varchar),'%Y%m') FROM .................t3_mercado_bpe_prestamo_tarjeta)
)
Revise this Paste