Building a filter/fxn for ETL to pick the earlier of two dates when applicable?
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
Best 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!**2
Answers
-
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!**2
Categories
- 10.5K All Categories
- 5 Connect
- 915 Connectors
- 250 Workbench
- 459 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 185 Visualize
- 250 Beast Mode
- 2.1K Charting
- 11 Variables
- 16 Automate
- 354 APIs & Domo Developer
- 88 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 245 Distribute
- 62 Domo Everywhere
- 242 Scheduled Reports
- 20 Manage
- 41 Governance & Security
- 170 Product Ideas
- 1.2K Ideas Exchange
- 10 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive