Crosstab With column Sum(values) and Count(nrDocuments) by Entities

• luissantos

  SSCertifiable

  Points: 7391

  More actions

  May 11, 2018 at 3:35 pm

  #334694

  Hello communty,

  I try to build  a crosstab  that  return  for  each entity 2 columns, one with the value of document and another one for Counting the number of documents.
  My problem  is  when the values  is Zero the column for counting documents return 1

  Here is my query:
  
DECLARE @DataIni AS DATETIME
DECLARE @DataFim AS DATETIME

  SET @DataIni = '20180101'
SET @DataFim = '20180131'

  SELECT DISTINCT
        identificacao1 [Entidade],
      SUM(CASE WHEN obranome LIKE 'C%' THEN etotaldeb ELSE 0 END) AS [Citologia],
        COUNT(distinct (CASE WHEN obranome LIKE 'C%' AND etotaldeb <> 0 THEN obrano ELSE 0 END)) AS[Total Citologias],   
      SUM(CASE WHEN obranome LIKE 'H%' THEN etotaldeb ELSE 0 END) AS [Histologia],
   COUNT(distinct (CASE WHEN obranome LIKE 'H%'THEN obrano ELSE 0 END)) AS [Total Histologia] ,
      SUM(CASE WHEN obranome LIKE 'AT%' THEN etotaldeb ELSE 0 END) AS [HPV-GT],
      COUNT(distinct (CASE WHEN obranome LIKE 'AT%' THEN obrano ELSE 0 END)) [Total HPV-GT],
      SUM(CASE WHEN obranome LIKE 'GT%' THEN etotaldeb ELSE 0 END) AS [Genotipagem HPV],
      COUNT(distinct (CASE WHEN obranome LIKE 'GT%' THEN obrano ELSE 0 END)) [Total Genotipagem HPV],
      SUM(CASE WHEN obranome LIKE 'E%' THEN etotaldeb ELSE 0 END) AS [Especiais],
      COUNT(distinct (CASE WHEN obranome LIKE 'E%' THEN obrano ELSE 0 END)) [Total Especiais],
      SUM(CASE WHEN obranome LIKE 'X%' or obranome LIKE 'F%' THEN etotaldeb ELSE 0 END) AS [Outros],
      COUNT(distinct (CASE WHEN obranome LIKE 'X%' or obranome LIKE 'F%' THEN obrano ELSE 0 END)) [Total Outros],
          SUM(Isnull(Etotaldeb,0)) [Total Geral]
FROM BO (nolock)
INNER JOIN bo2 (Nolock)
ON bo2.bo2stamp = bo.bostamp
WHERE
        bo.ndos = 12
        AND bo.dataobra BETWEEN @DataIni AND @DataFim
        AND bo2.anulado = 0
        AND bo.fechada = 0
GROUP BY identificacao1


  Someone could give me some help.
  Many thanks,
  Ls

• frederico_fonseca

  SSCoach

  Points: 16011

  More actions

  May 12, 2018 at 5:53 am

  #1990266

  on the counts replace the "else 0" with "else null"
  e.g change
  COUNT(distinct (CASE WHEN obranome LIKE 'C%' AND etotaldeb <> 0 THEN obrano ELSE 0 END)
  to
  COUNT(distinct (CASE WHEN obranome LIKE 'C%' AND etotaldeb <> 0 THEN obrano ELSE null END)