# Substitute for if / then statements

Member
edited August 27

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?

• Coach

```COALESCE(`(A2) Posted Bill Rate`, `(A1) Original Bill Rate`) + COALESCE(`(B2) Posted Bill Adj`, `(B1) Original Bill Adj`)
```

COALESCE takes the first non-null 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.

**Did this solve your problem? Accept it as a solution!**
• Coach

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 non-NULL 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! ✔️**

• Coach

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! ✔️**

• Coach

```COALESCE(`(A2) Posted Bill Rate`, `(A1) Original Bill Rate`) + COALESCE(`(B2) Posted Bill Adj`, `(B1) Original Bill Adj`)
```

COALESCE takes the first non-null 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.

**Did this solve your problem? Accept it as a solution!**
• Member

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`)`

• Coach