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.
– Ernesto Casanova
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.
– Ernesto Casanova
What is the database manager: Mysql or SQL Server? In SQL Server there is no to_date function()
– José Diz
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.
– anonimo
@Josédiz is Mysql.
– Ademir Junior
@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!
– Ademir Junior
" 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.
– Motta