Substitute for if / then statements
Hi  I'm trying to figure out the SQL code to sum (A) bill rate + (B) bill adjustment = Total Bill. However, Bill Rate can either be (A1) Original Bill Rate or (A2) Posted Bill Rate. Similarly, Bill Adjustment can either be (B1) Original Bill Adj or (B2) Posted Bill Adj.
Logic is that If the (A2)Posted Bill Rate is NULL, use (A1) Original Bill Rate. If the (B2) Posted Bill Adj is NULL, use (B1) Original Bill Adj.
So the final sum could be any of the following:
(A1) Original Bill Rate + (B1) Original Bill Adj = Total Bill
(A1) Original Bill Rate + (B2) Posted Bill Adj = Total Bill
(A2) Posted Bill Rate + (B1) Original Bill Adj = Total Bill
(A2) Posted Bill Rate + (B2) Posted Bill Adj = Total Bill
Is there a code / formula that can be written to account for the logic and do the addition in one formula?
Best Answers

COALESCE(`(A2) Posted Bill Rate`, `(A1) Original Bill Rate`) + COALESCE(`(B2) Posted Bill Adj`, `(B1) Original Bill Adj`)
COALESCE takes the first nonnull value in the parameters passed. So this is saying Use A2 Posted Bill Rate if it's not null, otherwise use (A1) Original Bill Rate. Add that to B2 otherwise add it to B1.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
The main difference between the two is that the IFNULL function takes two arguments and returns the first one if it's not NULL or the second if the first one is NULL.
The COALESCE function can take two or more parameters and returns the first nonNULL parameter, or NULL if all parameters are null.
In your case, either one will work.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**1
Answers

IFNULL(Posted Bill Rate, Original Bill Rate) + IFNULL(Posted Bill Adj, Original Bill Adj)
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**0 
COALESCE(`(A2) Posted Bill Rate`, `(A1) Original Bill Rate`) + COALESCE(`(B2) Posted Bill Adj`, `(B1) Original Bill Adj`)
COALESCE takes the first nonnull value in the parameters passed. So this is saying Use A2 Posted Bill Rate if it's not null, otherwise use (A1) Original Bill Rate. Add that to B2 otherwise add it to B1.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
Thank you both! Is there any difference between the 2 suggestions that I should consider? Does it matter if I'm using this within data flow that feeds a lot of other datasets?
IFNULL(Posted Bill Rate, Original Bill Rate) + IFNULL(Posted Bill Adj, Original Bill Adj)
COALESCE
(`(A2) Posted Bill Rate`, `(A1) Original Bill Rate`) +
COALESCE
(`(B2) Posted Bill Adj`, `(B1) Original Bill Adj`)
0 
The main difference between the two is that the IFNULL function takes two arguments and returns the first one if it's not NULL or the second if the first one is NULL.
The COALESCE function can take two or more parameters and returns the first nonNULL parameter, or NULL if all parameters are null.
In your case, either one will work.
David Cunningham
** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**1
Categories
 All Categories
 1.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 292 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 95 SQL DataFlows
 602 Datasets
 2.1K Magic ETL
 3.7K Visualize
 2.4K Charting
 695 Beast Mode
 43 App Studio
 39 Variables
 658 Automate
 170 Apps
 441 APIs & Domo Developer
 42 Workflows
 5 DomoAI
 32 Predict
 12 Jupyter Workspaces
 20 R & Python Tiles
 386 Distribute
 111 Domo Everywhere
 269 Scheduled Reports
 6 Software Integrations
 113 Manage
 110 Governance & Security
 8 Domo University
 30 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 98 Community Announcements
 Domo Community Gallery
 4.8K Archive