Show value 0 when no data is available

Asked

Viewed 47 times

3

To show in an sql query, value 0 when there is no data in the table?

SELECT
ae.dt_data,                  
to_char(ae.dt_data, 'dy'),
case
when to_char(ae.dt_data, 'dy') = 'sun' then 'Domingo'
when to_char(ae.dt_data, 'dy') = 'mon' then 'Segunda'
when to_char(ae.dt_data, 'dy') = 'tue' then 'Terça'
when to_char(ae.dt_data, 'dy') = 'wed' then 'Quarta'
when to_char(ae.dt_data, 'dy') = 'thu' then 'Quinta'
when to_char(ae.dt_data, 'dy') = 'fri' then 'Sexta'
when to_char(ae.dt_data, 'dy') = 'sat' then 'Sabado'
end as dia_semana,
count(DISTINCT ae.nr_controle) as qtde
                                       
FROM atendimentos  ae                                        
join exames        ex using(cd_atendimento)                
join procedimentos pr using(cd_procedimento)                 
join modalidades   mo using(cd_modalidade)                   
join pacientes     pa using(cd_paciente)                     
join salas         sa using(cd_sala)                         
join medicos       me on me.cd_medico = ae.cd_medico         
join planos        pl on pl.cd_plano  = ex.cd_plano          
join empresas      em on sa.cd_empresa = em.cd_empresa left  
join medicos       ms on ms.cd_medico = ex.cd_medico   left  
join atendimentos_localizacao al on al.cd_localizacao = ae.cd_localizacao

WHERE ae.dt_data >= now() :: date - 7 AND ae.dt_data < now() :: date

and  sa.cd_empresa = 7
and  ae.cd_sala in (74, 4, 121, 6, 7, 8, 75, 122, 91, 11, 111, 12, 134)
and  pr.cd_modalidade in (40, 14, 12, 19, 18, 15, 38, 17, 33, 20, 1, 16, 64)
and  pr.cd_procedimento in (627, 628, 631, 629, 635, 634, 630, 633, 632, 636, 669, 915, 604, 620, 616, 606, 608, 625, 610, 646, 828, 770, 614, 1013, 735, 618, 826)
and  ae.nr_controle is not null -- Descarta pacientes que não foram atendidos (Roxo ou Preto)

group by dt_data

In this above query he provides me with this data:

Dia_semana  |   qtde
Quinta      |   57
Sexta       |   64
Sabado      |   1
Segunda     |   80
Terça       |   64
Quarta      |   54

Since Domingo had no data to be consulted, he did not show in the result. I would like it to appear Sunday = 0, same example below for days of the week that there is no data.

Dia_semana  |   qtde
Quinta      |   57
Sexta       |   64
Sabado      |   1
Domingo     |   0
Segunda     |   80
Terça       |   64
Quarta      |   54

I tried to consult using coalesce and could not.

The bank is Postgres.

sql postgresql
  • 1

    Use the function generate_series(start, stop, step interval) to generate the table with the days to be considered and make a LEFT OUTER JOIN with the result of your query, not forgetting to put the COALESCE in your COUNT.

  • Instead of these Cases it would not be better to use Day in function to_char and the appropriate location?

1 answer

1

Making a left outer join with a table fake must solve...

WITH vazio as 
(
  SELECT NOW() dt_data UNION ALL
  SELECT NOW() - INTERVAL '1 day' dt_data UNION ALL
  SELECT NOW() - INTERVAL '2 day' dt_data UNION ALL
  SELECT NOW() - INTERVAL '3 day' dt_data UNION ALL
  SELECT NOW() - INTERVAL '4 day' dt_data UNION ALL
  select NOW() - INTERVAL '5 day' dt_data UNION ALL
  SELECT NOW() - INTERVAL '6 day' dt_data
)

select v.dt_data, 
       case
        when to_char(v.dt_data, 'dy') = 'sun' then 'Domingo'
        when to_char(v.dt_data, 'dy') = 'mon' then 'Segunda'
        when to_char(v.dt_data, 'dy') = 'tue' then 'Terça'
        when to_char(v.dt_data, 'dy') = 'wed' then 'Quarta'
        when to_char(v.dt_data, 'dy') = 'thu' then 'Quinta'
        when to_char(v.dt_data, 'dy') = 'fri' then 'Sexta'
        when to_char(v.dt_data, 'dy') = 'sat' then 'Sabado'
        end as dia_semana,
       count(DISTINCT ae.nr_controle) as qtde
  from vazio v
  left outer join atendimentos ae
    on cl.dat_err :: date = v.dt_data :: date
  join exames        ex using(cd_atendimento)                
  join procedimentos pr using(cd_procedimento)                 
  join modalidades   mo using(cd_modalidade)                   
  join pacientes     pa using(cd_paciente)                     
  join salas         sa using(cd_sala)                         
  join medicos       me 
    on me.cd_medico = ae.cd_medico         
  join planos        pl 
    on pl.cd_plano  = ex.cd_plano          
  join empresas      em 
    on sa.cd_empresa = em.cd_empresa 
  left outer join medicos ms 
    on ms.cd_medico = ex.cd_medico   left  
  join atendimentos_localizacao al on al.cd_localizacao = ae.cd_localizacao
 WHERE ae.dt_data >= now() :: date - 7 AND ae.dt_data < now() :: date
   and sa.cd_empresa = 7
   and ae.cd_sala in (74, 4, 121, 6, 7, 8, 75, 122, 91, 11, 111, 12, 134)
   and pr.cd_modalidade in (40, 14, 12, 19, 18, 15, 38, 17, 33, 20, 1, 16, 64)
   and pr.cd_procedimento in (627, 628, 631, 629, 635, 634, 630, 633, 632, 636, 669, 915, 604, 620, 616, 606, 608, 625, 610, 646, 828, 770, 614, 1013, 735, 618, 826)
   and  ae.nr_controle is not null -- Descarta pacientes que não foram atendidos (Roxo ou Preto)
 group by v.dt_data :: date

Q.S.: As you do not have the tables script I did not test with this query. But I believe it will work as well.

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.