Using Count(*) as a table filter

Asked

Viewed 271 times

0

Good morning, I’m breaking my head with the following code:

select
ac.cd_agenda as "Código Agenda",
m.nm_pessoa_fisica as "Médico",
esp.ds_especialidade as "Especialidade",
a.ds_complemento as "Agenda",
to_date(ac.dt_agenda, 'dd/mm/yy') "Data",
count(ac.cd_convenio) as "Convênios na Data",
acr.qt_permissao as "Cota",
a.ds_orientacao as Rótulo

from agenda_consulta ac

       join agenda a on a.cd_agenda = ac.cd_agenda
       join setor_atendimento s on s.cd_setor_atendimento = a.cd_setor_agenda
       join pessoa_fisica m on m.cd_pessoa_fisica = a.cd_pessoa_fisica 
       join agrupamento_setor ase on ase.nr_sequencia = s.nr_seq_agrupamento
       join especialidade_medica esp on a.cd_especialidade = esp.cd_especialidade
       join agenda_consulta_regra acr on acr.cd_agenda = ac.cd_agenda

where ac.cd_convenio = '55'
and acr.cd_convenio = '55'
and ac.dt_agenda > sysdate
and acr.ie_periodo = 'D'

group by ac.cd_agenda,
m.nm_pessoa_fisica, 
esp.ds_especialidade,
a.ds_complemento,
to_date(ac.dt_agenda, 'dd/mm/yy'),
a.ds_orientacao,
acr.qt_permissao

having count(ac.cd_convenio) < acr.qt_permissao

order by ac.cd_agenda

The current table returns me all dates where the number of agreements is <= to the quota value of this agreement. But I need the table to return me from these dates only the days on which the criterion

ac.ie_status_agenda = 'L'

is hit.

The problem is, in order to count how many patients we have for the insurance I had to include the filter

ac.cd_convenio = '55'

and ie_status_agenda = 'L' always has the value of cd_convenio null.

I am available for further clarification, my first time posting here, I do not know very well if I made myself clear.

I appreciate any suggestion!

  • Hello welcome, take a look at code of conduct.

  • 1

    Also take the opportunity to improve your question, put the current and expected output example. You can use the Sqlfiddle to create an example data Seed and it is easier to help you in your difficulty.

  • What is the database manager: Mysql or SQL Server? In SQL Server there is no to_date function()

  • Since you don’t want to consider all the records but only the previous records I believe you need to use a CTE (Common Table Expressions) in a WITH.

  • @Josédiz is Mysql.

  • @Ernestocasanova I will try, I am not very familiar with this site, but if I can put there I put here for you to see. Thanks!

  • " include the ac.cd_convenio = '55' and ie_status_agenda = 'L' filter always has the value of cd_convenio null. " or I don’t understand or you have a test incompatibility.

Show 2 more comments
No answers

Browser other questions tagged

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