Help with SQL CASE WHEN using % Query

Question

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

Answer 1

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