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 .................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