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

slewis1
slewis1
edited March 14 in Magic ETL

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

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    edited November 2022 Answer ✓

    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`)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    edited November 2022 Answer ✓

    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`)
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories