Ir ao conteúdo
  • Cadastre-se
jaminhum

RESOLVIDO Trazer valores como NULL!

Recommended Posts

Olá como faço para que os dias após a data de hoje apareçam como NULL para o ano somente de 2015, por exemplo hoje é dia 19, dessa forma do dia 20 ao 31, no ano de 2015, os valores devem ser NULL ao invés de 0. Abaixo está o código e o resultado.

SELECT date_part('year', data) as ano,         SUM(CASE WHEN date_part('day',data) = '1' THEN 1 ELSE 0 END) AS "01",        SUM(CASE WHEN date_part('day',data) = '2' THEN 1 ELSE 0 END) AS "02",        SUM(CASE WHEN date_part('day',data) = '3' THEN 1 ELSE 0 END) AS "03",        SUM(CASE WHEN date_part('day',data) = '4' THEN 1 ELSE 0 END) AS "04",        SUM(CASE WHEN date_part('day',data) = '5' THEN 1 ELSE 0 END) AS "05",        SUM(CASE WHEN date_part('day',data) = '6' THEN 1 ELSE 0 END) AS "06",        SUM(CASE WHEN date_part('day',data) = '7' THEN 1 ELSE 0 END) AS "07",        SUM(CASE WHEN date_part('day',data) = '8' THEN 1 ELSE 0 END) AS "08",        SUM(CASE WHEN date_part('day',data) = '9' THEN 1 ELSE 0 END) AS "09",        SUM(CASE WHEN date_part('day',data) = '10' THEN 1 ELSE 0 END) AS "10",        SUM(CASE WHEN date_part('day',data) = '11' THEN 1 ELSE 0 END) AS "11",        SUM(CASE WHEN date_part('day',data) = '12' THEN 1 ELSE 0 END) AS "12",        SUM(CASE WHEN date_part('day',data) = '13' THEN 1 ELSE 0 END) AS "13",        SUM(CASE WHEN date_part('day',data) = '14' THEN 1 ELSE 0 END) AS "14",        SUM(CASE WHEN date_part('day',data) = '15' THEN 1 ELSE 0 END) AS "15",        SUM(CASE WHEN date_part('day',data) = '16' THEN 1 ELSE 0 END) AS "16",        SUM(CASE WHEN date_part('day',data) = '17' THEN 1 ELSE 0 END) AS "17",        SUM(CASE WHEN date_part('day',data) = '18' THEN 1 ELSE 0 END) AS "18",        SUM(CASE WHEN date_part('day',data) = '19' THEN 1 ELSE 0 END) AS "19",        SUM(CASE WHEN date_part('day',data) = '20' THEN 1 ELSE 0 END) AS "20",        SUM(CASE WHEN date_part('day',data) = '21' THEN 1 ELSE 0 END) AS "21",        SUM(CASE WHEN date_part('day',data) = '22' THEN 1 ELSE 0 END) AS "22",        SUM(CASE WHEN date_part('day',data) = '23' THEN 1 ELSE 0 END) AS "23",        SUM(CASE WHEN date_part('day',data) = '24' THEN 1 ELSE 0 END) AS "24",        SUM(CASE WHEN date_part('day',data) = '25' THEN 1 ELSE 0 END) AS "25",        SUM(CASE WHEN date_part('day',data) = '26' THEN 1 ELSE 0 END) AS "26",        SUM(CASE WHEN date_part('day',data) = '27' THEN 1 ELSE 0 END) AS "27",        SUM(CASE WHEN date_part('day',data) = '28' THEN 1 ELSE 0 END) AS "28",        SUM(CASE WHEN date_part('day',data) = '29' THEN 1 ELSE 0 END) AS "29",        SUM(CASE WHEN date_part('day',data) = '30' THEN 1 ELSE 0 END) AS "30",        SUM(CASE WHEN date_part('day',data) = '31' THEN 1 ELSE 0 END) AS "31",        COUNT(*) as Totalfrom admgis.controle_mortesWHERE subjetividade = 'CVLI'AND date_part('MONTH',data) = 01AND UPPER(cidade) = 'ARAPIRACA'GROUP BY anoORDER BY ano

1sfuao.jpg

Compartilhar este post


Link para o post
Compartilhar em outros sites

Essa foi a solução encontrada:

SELECT date_part('year', data)as ano	, SUM(CASE WHEN date_part('day',data) = '1' THEN 1 ELSE (CASE WHEN ('1' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "01",SUM(CASE WHEN date_part('day',data) = '2' THEN 1 ELSE (CASE WHEN ('2' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "02",SUM(CASE WHEN date_part('day',data) = '3' THEN 1 ELSE (CASE WHEN ('3' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "03",SUM(CASE WHEN date_part('day',data) = '4' THEN 1 ELSE (CASE WHEN ('4' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "04",SUM(CASE WHEN date_part('day',data) = '5' THEN 1 ELSE (CASE WHEN ('5' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "05",SUM(CASE WHEN date_part('day',data) = '6' THEN 1 ELSE (CASE WHEN ('6' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "06",SUM(CASE WHEN date_part('day',data) = '7' THEN 1 ELSE (CASE WHEN ('7' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "07",SUM(CASE WHEN date_part('day',data) = '8' THEN 1 ELSE (CASE WHEN ('8' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "08",SUM(CASE WHEN date_part('day',data) = '9' THEN 1 ELSE (CASE WHEN ('9' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "09",SUM(CASE WHEN date_part('day',data) = '10' THEN 1 ELSE (CASE WHEN ('10' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "10",SUM(CASE WHEN date_part('day',data) = '11' THEN 1 ELSE (CASE WHEN ('11' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data))THEN 0 ELSE null END) END) AS "11",SUM(CASE WHEN date_part('day',data) = '12' THEN 1 ELSE (CASE WHEN ('12' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "12",SUM(CASE WHEN date_part('day',data) = '13' THEN 1 ELSE (CASE WHEN ('13' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "13",SUM(CASE WHEN date_part('day',data) = '14' THEN 1 ELSE (CASE WHEN ('14' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "14",SUM(CASE WHEN date_part('day',data) = '15' THEN 1 ELSE (CASE WHEN ('15' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "15",SUM(CASE WHEN date_part('day',data) = '16' THEN 1 ELSE (CASE WHEN ('16' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "16",SUM(CASE WHEN date_part('day',data) = '17' THEN 1 ELSE (CASE WHEN ('17' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "17",SUM(CASE WHEN date_part('day',data) = '18' THEN 1 ELSE (CASE WHEN ('18' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "18",SUM(CASE WHEN date_part('day',data) = '19' THEN 1 ELSE (CASE WHEN ('19' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "19",SUM(CASE WHEN date_part('day',data) = '20' THEN 1 ELSE (CASE WHEN ('20' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "20",SUM(CASE WHEN date_part('day',data) = '21' THEN 1 ELSE (CASE WHEN ('21' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data))THEN 0 ELSE null END) END) AS "21",SUM(CASE WHEN date_part('day',data) = '22' THEN 1 ELSE (CASE WHEN ('22' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "22",SUM(CASE WHEN date_part('day',data) = '23' THEN 1 ELSE (CASE WHEN ('23' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data))THEN 0 ELSE null END) END) AS "23",SUM(CASE WHEN date_part('day',data) = '24' THEN 1 ELSE (CASE WHEN ('24' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "24",SUM(CASE WHEN date_part('day',data) = '25' THEN 1 ELSE (CASE WHEN ('25' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "25",SUM(CASE WHEN date_part('day',data) = '26' THEN 1 ELSE (CASE WHEN ('26' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "26",SUM(CASE WHEN date_part('day',data) = '27' THEN 1 ELSE (CASE WHEN ('27' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "27",SUM(CASE WHEN date_part('day',data) = '28' THEN 1 ELSE (CASE WHEN ('28' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "28",SUM(CASE WHEN date_part('day',data) = '29' THEN 1 ELSE (CASE WHEN ('29' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "29",SUM(CASE WHEN date_part('day',data) = '30' THEN 1 ELSE (CASE WHEN ('30' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "30",SUM(CASE WHEN date_part('day',data) = '31' THEN 1 ELSE (CASE WHEN ('31' <= date_part('day', now()) OR date_part('year', now()) <> date_part('year', data)) THEN 0 ELSE null END) END) AS "31",COUNT(*) as Totalfrom controle_mortesWHERE subjetividade = 'CVLI'AND date_part('MONTH', data) = 01AND UPPER(cidade) = 'ARAPIRACA'GROUP BY anoORDER BY ano;

Compartilhar este post


Link para o post
Compartilhar em outros sites

Crie uma conta ou entre para comentar

Você precisar ser um membro para fazer um comentário

Criar uma conta

Crie uma nova conta em nossa comunidade. É fácil!

Crie uma nova conta

Entrar

Já tem uma conta? Faça o login.

Entrar agora





Sobre o Clube do Hardware

No ar desde 1996, o Clube do Hardware é uma das maiores, mais antigas e mais respeitadas publicações sobre tecnologia do Brasil. Leia mais

Direitos autorais

Não permitimos a cópia ou reprodução do conteúdo do nosso site, fórum, newsletters e redes sociais, mesmo citando-se a fonte. Leia mais

×