select
Paciente= HC.IdPaciente ,
Fecha_Adm = CONVERT(char(10), E.FechaAdmision, 101),
Activo = Case when E.Estado = 'Activo' then 1 end,
Anulado = Case when E.Estado = 'Anulado' then 1 end,
Estado = Case when E.Estado = 'Anulado' then 'Solo Anulado'
when E.Estado = 'Activado' and E.Estado= 'Anulado' THEN 'Activado' else 'Activado' end
from dbo.VW_SA_EM_REP_ListadoAtencionEmergencia E
--LEFT JOIN PersonaMast Paciente ON Cita.IdPaciente = Paciente.Persona
LEFT JOIN SS_GE_Paciente HC ON E.CodigoHC = HC.CodigoHC
where
Year(FechaAdmision)=2018
and Month(FechaAdmision)=08
and TipoAtencion= 'Emergencia'
and E.Sucursal = 'CEG'
group by
HC.IdPaciente,
CONVERT(char(10), E.FechaAdmision, 101),
Case when E.Estado = 'Activo' then 1 end,
Case when E.Estado = 'Anulado' then 1 end,
Case when E.Estado = 'Anulado' then 'Solo Anulado'
when E.Estado = 'Activado' and E.Estado= 'Anulado' THEN 'Activado' else 'Activado' end
order by Paciente, Fecha_Adm
I have this query, from which the result gives me an example if a patient has id
28 and its FechaAdmsion
equals 01/10
but has two states Activo
and Anulado
.
Based on this example I will give two data that one has Activated and the other Canceled but the question would be how to make this patient who has two data put only one.