with
hist_clients_query as (
select distinct
cli."clientCode",
cli.name,
cli.surname
from
hist_clients cli
)
select
ae."clientCode",
COALESCE(
rtrim(cli.name || ' ' || cli.surname),
rtrim(ae."clientName" || ' ' || ae."clientLastName")
) as "fullName",
count(distinct ae.reason) "cantidad de razones"
from
audit_excluded ae
left join hist_clients_query cli on ae."clientCode" = cli."clientCode"
where
(
ae."ticketProcessId" = 0
or ae."ticketProcessId" = (
select
tp.id
from
ticket_process tp
where
tp.status = 'completed'
and tp."raffleId" = ae."raffleId"
)
)
and ae."raffleId" = 47
-- and ae."clientCode" = '973810'
group by
"ae"."clientCode",
"fullName"