Sql-server – SQL Server: Case expression returns NULL even with an ELSE

casesql server

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.