Tricky Date Datatype Question

Hello all,

I have tricky question related to date fields. here i explain with a simple scenario.

Consider a table A with about 100000 patient stay records. the table has facilitypatientkey, staybegineffectivedate, stayendeffectivedate columns. There can a more than one record for a patient since same patient can stay and get discharged more than once. For example


facilitypatientkey stays from '1/1/2000' to '5/25/2000'
facilitypatientkey stays from '12/20/2000' to '3/15/2001'

I want to return the latest end date of each patient. This is pretty straightforward we can just group by facilitypatientkey and return max(enddate) like below

Select facilitypatientkey,maX(stayendeffectivedate) AS MaxEndDate from dw_patient_stay
where facilitypatientkey IN (Select facilitypatientkey from dw_patient_stay)
group by facilitypatientkey
order by facilitypatientkey

but the tricky part is, if the latest end date is 'null' then i want to return as 'null' for that patient.

For example,
facilitypatientkey stays from '1/1/2000' to '5/25/2000'
facilitypatientkey stays from '12/20/2000' to '3/15/2001'
facilitypatientkey stays from '4/12/2007' to null(which actually means they still stay)

now the above query ll return '3/15/2001' instead of 'null'. Can anyone help me with this?

I hope you got the picture.

Thanks,
Praveen