Does anyone know why this case expression would be returning NULLs even when there is an ELSE? It returns 'Normal' and 'Premature', but also numerous NULLs.
select distinct(external_id), encounter_date,
(select
case
when h2.encounter_date < DATEADD(WEEK, -3, isnull(f2.edd_final_date,'01-jan-1900'))
and h.encounter_date < DATEADD(dd, 29, isnull(t2.date_value,'01-jan-1900'))
then 'Premature-Emergency'
when h2.encounter_date < DATEADD(WEEK, -3, isnull(f2.edd_final_date,'01-jan-1900'))
then 'Premature'
when h.encounter_date < DATEADD(dd, 29, isnull(t2.date_value,'01-jan-1900'))
then 'Emergency'
else 'Normal'
end
from enhfile h2
inner join ar00file a2 on a2.patient_no=h2.patient_no and a2.trans_date=h2.encounter_date
inner join endfile d2 on d2.patient_no=h2.patient_no and d2.encounter_no=h2.encounter_no
and d2.charge_no=a2.charge_no
left join fmhfile f2 on f2.patient_no = h2.patient_no
left join notesdtl t2 on t2.patient_no=n.patient_no and t2.note_id=n.note_id
and element_id in ('4559','4560')
where h2.patient_no=a.patient_no and h2.encounter_date=
(select MaxEncDate
from (select MAX(h4.encounter_date) as MaxEncDate from enhfile h4
inner join ENDFILE d4 on h4.PATIENT_NO=d4.patient_no and h4.ENCOUNTER_NO=d4.encounter_no
where h4.patient_no=h2.patient_no
and a2.procedure_code in ('59409','59410','59400','59514','59515','59510','59415')
) v
)
and f2.history_date=(select MaxHxDate
from (select MAX(f3.history_date) as MaxHxDate from fmhfile f3
where f3.patient_no=h2.patient_no
) w
)
group by h2.patient_no, h2.encounter_no, t2.date_value, h2.encounter_date, f2.edd_final_date)
as Circumstance
from ar00file a
inner join mpfile m on m.patient_no=a.patient_no
inner join exidfile x on m.patient_no=x.patient_no
inner join chhfile c on a.procedure_code=c.procedure_code
inner join enhfile h on a.patient_no=h.patient_no and a.trans_date=h.encounter_date
inner join endfile d on h.patient_no=d.patient_no and h.encounter_no=d.encounter_no
left join fmhfile f on f.patient_no=h.patient_no
left join noteshdr n on n.patient_no=h.patient_no and n.note_date_time=h.encounter_date
and n.note_type='FSP'
left join notesdtl t on t.patient_no=n.patient_no and t.note_id=n.note_id
and element_id in ('4559','4560')
where d.charge_no=a.charge_no
and a.trans_date between '01-jan-2016' and '30-jun-2016'
and a.PROCEDURE_CODE in ('58600','58611','58670','58671','58150','58150GY','58180GY','59525')
Best Answer
I would try adding some ISNULL functions to the left sides of the Case checks (h.encounter_date and h2.encounter_date), as I don't see a WHERE h.encounter_date/h2.encounter_date IS NOT NULL in your WHERE clause.