A little help with a simple CASE statement

Question

A little help with a simple CASE statement

seubanks00

Mr or Mrs. 500

Points: 591
More actions
March 24, 2017 at 11:42 am

#319910

What am I doing wrong with this case statement? I am trying to get a calculated 'setup_cost' where the formula is SETUP_TIME divided by SETUP_QUANTITY multiplied by a LABOR_RATE. SETUP_TIME is usually zero except for one operation. This means that the calculated setup_cost will be zero for most operations. I thought I could use a case statement here to avoid dividing by Zero but I am still getting a divide by zero error. Any help is appreciated.
SELECT CASE
   WHEN SETUP_TIME = 0 THEN '0'
   ELSE (SETUP_TIME / SETUP_QUANTITY) * LABOR_RATE
   END AS 'Setup_Cost'

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

seubanks00 - Friday, March 24, 2017 11:42 AM
What am I doing wrong with this case statement? I am trying to get a calculated 'setup_cost' where the formula is SETUP_TIME divided by SETUP_QUANTITY multiplied by a LABOR_RATE. SETUP_TIME is usually zero except for one operation. This means that the calculated setup_cost will be zero for most operations. I thought I could use a case statement here to avoid dividing by Zero but I am still getting a divide by zero error. Any help is appreciated.
SELECT CASE
   WHEN SETUP_TIME = 0 THEN '0'
   ELSE (SETUP_TIME / SETUP_QUANTITY) * LABOR_RATE
   END AS 'Setup_Cost'

You are testing if SETUP_TIME is zero, not if SETUP_QUANTITY is zero.

seubanks00 Mr or Mrs. 500 Points: 591 More actions · Answer 2

Correct - if the SETUP_TIME is equal to zero then I want to return zero. If the SETUP_TIME is other than zero I want to return the result of SETUP_TIME divided by SETUP_QUANTITY multiplied by LABOR_RATE. Am I making sense?

Thanks for taking the time to respond!

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

seubanks00 - Friday, March 24, 2017 12:04 PM
Correct - if the SETUP_TIME is equal to zero then I want to return zero. If the SETUP_TIME is other than zero I want to return the result of SETUP_TIME divided by SETUP_QUANTITY multiplied by LABOR_RATE. Am I making sense?
Thanks for taking the time to respond!

What do you think will happen if SETUP_QUANTITY is zero?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

seubanks00 Mr or Mrs. 500 Points: 591 More actions · Answer 4

ChrisM@home - Friday, March 24, 2017 12:19 PM
seubanks00 - Friday, March 24, 2017 12:04 PM
Correct - if the SETUP_TIME is equal to zero then I want to return zero. If the SETUP_TIME is other than zero I want to return the result of SETUP_TIME divided by SETUP_QUANTITY multiplied by LABOR_RATE. Am I making sense?
Thanks for taking the time to respond!
What do you think will happen if SETUP_QUANTITY is zero?

I'm an idiot. Thank you! I was sure it was something I was doing wrong or not understanding how CASE worked rather than realizing that the SETUP_QUANTITY could be zero as well.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

seubanks00 - Friday, March 24, 2017 12:43 PM
ChrisM@home - Friday, March 24, 2017 12:19 PM
seubanks00 - Friday, March 24, 2017 12:04 PM
Correct - if the SETUP_TIME is equal to zero then I want to return zero. If the SETUP_TIME is other than zero I want to return the result of SETUP_TIME divided by SETUP_QUANTITY multiplied by LABOR_RATE. Am I making sense?
Thanks for taking the time to respond!
What do you think will happen if SETUP_QUANTITY is zero?
I'm an idiot. Thank you! I was sure it was something I was doing wrong or not understanding how CASE worked rather than realizing that the SETUP_QUANTITY could be zero as well.

You do remember that zero divided by anything is still zero, right? You only need to test if SETUP_QUANTITY is zero to avoid a divide by zero error.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

I also recommend that you stop using quoted identifiers for aliases and that you make the resulting datatype for the THEN and ELSE be the same so that you don't have the addition overhead of implicit conversions.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)