Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

I'm having trouble filtering display

User_AC3QEFeb 11 2021

I'm having trouble trying not to display the line 'NOT ISOLATED' when there is more than one item in the "ds_motivo" column, with the same code in the "cd_setor" column and the same date in the "dt_referencia" column
example should only display the first line of the result.
as line 3 has more items in the ds_motivo column and with the same code in the "Cd_setor" column and the same date in the "dt_referencia" column, I don't want that item to be displayed.
result example
01/01/2020 411 Unidade Coronária - DE 07º andar - Ala III does not appear
image.pngSQL

with temp as
(select z.dt_referencia as dt_referencia,
                z.cd_setor,
                z.ds_setor,
                sum (decode (z.IE_TIPO, 'DIA', decode (z.ie_status, 'Busy', 1,0), 0)) as qt_paciente_dia
from hsl_censo_bi z
where z.ie_tipo = 'DIA'
and z.dt_referencia between: dt_inicio and: dt_fim
and z.nr_atendimento is not null
and z.cd_establishment = 1
group by z.dt_referencia,
         z.cd_setor,
         z.ds_setor
order by 1)
select t.dt_referencia as dt_referencia,
                t.cd_sector,
                t.ds_setor to ds_setor,
                case when mi.ds_motivo is null then 'NOT ISOLATED'
                else mi.ds_motivo end as ds_motivo,
                t.qt_paciente_dia as qt_paciente_dia,
                count (mi.ds_motivo) as qt_motivo,
                round (((count (mi.ds_motivo) * 100) / t.qt_paciente_dia)) as col
               
from hsl_censo_bi a
left join temp t on a.dt_referencia = t.dt_referencia and a.cd_setor = t.cd_setor
left join ATENDIMENTO_PRECAUCAO APR ON APR.NR_ATENDIMENTO = A.NR_ATENDIMENTO
left join CIH_PRECAUCAO CP ON CP.NR_SEQUENCIA = APR.NR_SEQ_PRECAUCAO
left join MOTIVO_ISOLAMENTO MI ON MI.NR_SEQUENCIA = APR.NR_SEQ_MOTIVO_ISOL
where a.ie_tipo = 'DIA'
and a.dt_referencia between: dt_inicio and: dt_fim
and a.nr_atendimento is not null
and apr.dt_inativacao is null
and a.cd_establishment = 1
and t.cd_setor in (409,410,411)
group by t.dt_referencia,
                t.cd_sector,
                t.ds_sector,
                t.qt_paciente_dia,
                mi.ds_motivo
order by 1,3,4

Comments

Processing
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Mar 11 2021
Added on Feb 11 2021
1 comment
95 views