Trazer valores como NULL!

jaminhum · 22 de janeiro de 2015

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

jaminhum · 26 de janeiro de 2015

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;