Substitute for if / then statements

EChampagne
EChampagne Member
edited August 27 in Magic ETL

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

  • GrantSmith
    GrantSmith Coach
    Answer ✓ 

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • david_cunningham
    david_cunningham Coach
    Answer ✓

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

Answers

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

  • GrantSmith
    GrantSmith Coach
    Answer ✓ 

    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.

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

  • david_cunningham
    david_cunningham Coach
    Answer ✓

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

Categories