So I'm trying to combine values in each new column that I created in view with a case statement. How would I go about doing this?
SELECT
PI.Patient_UID, PI.FirstName, PI.LastName,
AP.ApptStatus, AP.VisitPosted, CD.TotalDue,
AP.Appointment_UID,
CASE
WHEN AP.VisitPosted = 0
THEN 'Visit Posted Error'
END Error1,
CASE
WHEN (CD.TotalDue IS NULL OR CD.TotalDue = '')
THEN 'Gross Charge Error'
END Error2
FROM
vw_ODBC_pt_PatientInfo AS PI
INNER JOIN
vw_ODBC_appts_Appointments AS AP ON AP.PatientFID = PI.Patient_UID
INNER JOIN
vw_ODBC_actv_ChargeDetail AS CD ON CD.PatientFID = PI.Patient_UID
WHERE
AP.ApptStatus NOT IN ('10', '11', '12');
I need the new columns Error1
and Error2
to be concatenated as such:
Error1 Error2 ErrorsCombined
Visit Posted Error Gross Charge Error Visit Posted Error;Gross Charge Error
Visit Posted Error NULL Visit Posted Error;
Visit Posted Error NULL Visit Posted Error;
Visit Posted Error NULL Visit Posted Error;
Any help would be greatly appreciated! Thanks in advance.
use APPLY
operator for the Error1
and Error2
expression. Make use of concat_ws() which perform string concatenation with separator and it handle NULL
value.
select PI.Patient_UID,
PI.FirstName,
PI.LastName,
AP.ApptStatus,
AP.VisitPosted,
CD.TotalDue,
AP.Appointment_UID,
e.Error1,
e.Error2,
concat_ws(';', e.Error1, e.Error2) as ErrorsCombined
from vw_ODBC_pt_PatientInfo as PI
inner join vw_ODBC_appts_Appointments as AP on AP.PatientFID = PI.Patient_UID
inner join vw_ODBC_actv_ChargeDetail as CD on CD.PatientFID = PI.Patient_UID
cross apply
(
select case when AP.VisitPosted = 0
then 'Visit Posted Error'
end as Error1,
case when (CD.TotalDue is null or CD.TotalDue = '')
then 'Gross Charge Error'
end as Error2
) e
where AP.ApptStatus not in ('10','11','12');