0

I have two columns in a SQL Query ( Estimate and Billed ) which contain un-formatted numbers.

I am trying to write a CASE WHEN query for when the BILLED column is between 50% and 70% of the BILLED column THEN '1' ELSE '0'

Then the same for 70% to 90% , 90% to 100% and then greater than 110% ( so multiple CASE WHEN ).

I am just not familiar with the arithmetic needed or if this is the best way to achieve this...

Same Query, with the CASE WHEN in plain English to help explain:

SELECT

CaseNumber,
EstimateAmt,
BilledAmt,

CASE WHEN BilledAmt IS BETWEEN 50% and 70% of EstimateAmt THEN '1' ELSE '0' end as 'IsBetween50And70',

CASE WHEN BilledAmt IS BETWEEN 70% and 90% of EstimateAmt THEN '1' ELSE '0' end as 'IsBetween70And90',

CASE WHEN BilledAmt IS BETWEEN 90% and 100% of EstimateAmt THEN '1' ELSE '0' end as 'IsBetween90And100',

CASE WHEN BilledAmt IS > 110% of EstimateAmt THEN '1' ELSE '0' end as 'IsGreaterThan110'


from CaseList

Thank you

  • 1
    un-formatted numbers - what does it mean? What is BilledAmt column type? – Denis Rubashkin Feb 12 at 9:44
  • Please edit your question to include sample data. – Colin 't Hart Feb 12 at 11:51
0

Please try below code for your issue:

SELECT
CaseNumber,
EstimateAmt,
BilledAmt,
(CASE WHEN BilledAmt IS BETWEEN ((50 * EstimateAmt)/100) and ((70 * EstimateAmt)/100) THEN '1' ELSE '0' end) as 'IsBetween50And70',
(CASE WHEN BilledAmt IS BETWEEN ((70 * EstimateAmt)/100) and ((90 * EstimateAmt)/100) THEN '1' ELSE '0' end) as 'IsBetween70And90',
(CASE WHEN BilledAmt IS BETWEEN ((90 * EstimateAmt)/100) and ((100 * EstimateAmt)/100) THEN '1' ELSE '0' end) as 'IsBetween90And100',
(CASE WHEN BilledAmt IS > (110* EstimateAmt)/100 THEN '1' ELSE '0' end) as 'IsGreaterThan110'
from CaseList
|improve this answer|||||
  • code type only answer is not recommended here, please check the rules/norms before answering. – Learning_DBAdmin Feb 12 at 10:23
  • This worked perfectly, once I removed the BETWEEN. Thank you very much. – Humble-Self Feb 12 at 11:07
  • I recommend formatting your code for a better reading. – Ronaldo Feb 12 at 12:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.