Building a filter/fxn for ETL to pick the earlier of two dates when applicable?

slewis1

I have a date set of patients that went through my healthcare organization. Some patients begin there encounter with an observation portion of their admit and some start directly as an inpatient. The patients that have an observation portion will have a observation start date and time in the data set and an inpatient start date and time. The patients that go directly to inpatient will not have observation data.

I am wanting to build a filter or function within the ETL that will pick the first date between the two. So patients with only an inpatient admission their start date/time would remain the inpatient start date/time and the patients that had a observation portion first it will select that earlier date/time over the later inpatient admit date/time

GrantSmith

You can use the LEAST function to select the smallest value of dates

LEAST(`Observation Date`, `Admit Date`)

If the observation date is null then you'll want to default it to the admit date via a COALESCE or IFNULL:

LEAST(COALESCE(`Observation Date`, `Admit Date`), `Admit Date`)

GrantSmith

You can use the LEAST function to select the smallest value of dates

LEAST(`Observation Date`, `Admit Date`)

If the observation date is null then you'll want to default it to the admit date via a COALESCE or IFNULL:

LEAST(COALESCE(`Observation Date`, `Admit Date`), `Admit Date`)