Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT ano
- ,mes
- ,CASE WHEN
- COALESCE(categoria,666) = 666 THEN maxcat ELSE categoria END cat
- FROM (
- SELECT ano
- ,mes
- ,maxcat
- ,COALESCE(cat,0) as cat,
- CASE WHEN
- Extract('Year' From Now()) >= ano AND Extract('Month' From Now()) > mes AND COALESCE(cat,0) = 0
- THEN 0
- ELSE cat END as categoria
- FROM (
- SELECT
- CAST(EXTRACT(YEAR FROM G) AS INTEGER ) AS ano
- ,CAST(EXTRACT(MONTH FROM G) AS INTEGER) AS mes
- ,F.categoria AS maxcat
- ,F.ano AS fim_ano
- ,F.mes AS fim_mes
- ,CASE
- WHEN CAST(EXTRACT(YEAR FROM G) AS INTEGER ) < 2017 AND COALESCE(T.categoria,0) <= 0 THEN 0
- WHEN CAST(EXTRACT(YEAR FROM G) AS INTEGER ) >= 2019 AND CAST(EXTRACT(MONTH FROM G) AS INTEGER) = T.mes AND COALESCE(T.categoria,0) = 0 THEN F.categoria
- ELSE T.categoria END AS cat
- FROM generate_series(DATE'2016-01-01',DATE'2022-01-01',INTERVAL '1 month') G
- LEFT JOIN(
- SELECT ano
- ,mes
- ,COALESCE(id_categoria,0) AS categoria
- FROM cadastros.oficinas_elegiveis
- WHERE id_oficina = 1691
- ) T ON 1=1 AND T.ano = CAST(EXTRACT(YEAR FROM G) AS INTEGER) AND T.mes = CAST(EXTRACT(MONTH FROM G) AS INTEGER)
- LEFT JOIN(
- SELECT max(id) as id, ano, mes,
- id_categoria AS categoria
- FROM cadastros.oficinas_elegiveis
- WHERE id_oficina = 1691
- GROUP BY id,ano,mes,id_categoria
- ORDER BY id DESC
- LIMIT 1
- ) F ON 1=1
- WHERE 1=1
- GROUP BY 1,2,3,4,5,6
- ORDER BY CAST(EXTRACT(YEAR FROM G) AS INTEGER )
- ) FOO
- WHERE 1=1
- order by ano,mes
- ) AM
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement