Substitute for if / then statements

EChampagne

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?

GrantSmith

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.

david_cunningham

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

IFNULL(Posted Bill Rate, Original Bill Rate) + IFNULL(Posted Bill Adj, Original Bill Adj)

GrantSmith

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.

EChampagne

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

david_cunningham

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.