daily pastebin goal
57%
SHARE
TWEET

Untitled

a guest Apr 20th, 2018 66 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT  ano
  2.        ,mes
  3.        ,CASE WHEN
  4.     COALESCE(categoria,666) = 666 THEN maxcat ELSE categoria END cat
  5.  
  6.  FROM (
  7.  
  8. SELECT ano
  9.       ,mes
  10.       ,maxcat
  11.       ,COALESCE(cat,0) as cat,
  12.       CASE WHEN
  13.          Extract('Year' From Now()) >= ano AND  Extract('Month' From Now()) > mes AND COALESCE(cat,0) = 0
  14.       THEN 0
  15.       ELSE cat END as categoria    
  16.      
  17.       FROM (
  18.  
  19.  
  20. SELECT
  21.    
  22.      CAST(EXTRACT(YEAR FROM G)   AS INTEGER ) AS ano
  23.     ,CAST(EXTRACT(MONTH FROM G) AS INTEGER) AS mes
  24.     ,F.categoria AS maxcat
  25.     ,F.ano AS fim_ano
  26.     ,F.mes AS fim_mes
  27.     ,CASE
  28.     WHEN CAST(EXTRACT(YEAR FROM G) AS INTEGER ) < 2017 AND COALESCE(T.categoria,0) <= 0 THEN 0
  29.         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
  30.     ELSE T.categoria END AS cat
  31.  
  32. FROM generate_series(DATE'2016-01-01',DATE'2022-01-01',INTERVAL '1 month') G
  33.  
  34.  
  35.     LEFT JOIN(
  36.             SELECT ano
  37.                 ,mes
  38.                 ,COALESCE(id_categoria,0) AS categoria
  39.  
  40.             FROM cadastros.oficinas_elegiveis
  41.                 WHERE id_oficina = 1691
  42.  
  43.          ) T ON 1=1 AND T.ano = CAST(EXTRACT(YEAR FROM G) AS INTEGER)  AND T.mes = CAST(EXTRACT(MONTH FROM G) AS INTEGER)
  44.  
  45.  
  46.     LEFT JOIN(
  47.             SELECT max(id) as id, ano, mes,
  48.                 id_categoria AS categoria
  49.              FROM cadastros.oficinas_elegiveis
  50.             WHERE id_oficina = 1691
  51.             GROUP BY id,ano,mes,id_categoria
  52.             ORDER BY id DESC
  53.             LIMIT 1
  54.          ) F ON 1=1
  55.  
  56.  
  57.  
  58. WHERE 1=1
  59. GROUP BY 1,2,3,4,5,6
  60. ORDER BY CAST(EXTRACT(YEAR FROM G)  AS INTEGER )
  61. ) FOO
  62. WHERE 1=1
  63. order by ano,mes
  64. ) AM
RAW Paste Data
Top