I have a sidewalk_inspection
table:
create table user.sidewalk_insp
(
insp_id int,
deficiency_length number(10,2),
sidewalk_length number(10,2)
);
insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 1, 0.0, 10.0);
insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 2, 2.5, 10.0);
insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 3, 5.0, 10.0);
insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 3, 7.5, 10.0);
insert into user.sidewalk_insp (insp_id, deficiency_length, sidewalk_length) values ( 4, 11.0, 10.0);
select * from user.sidewalk_insp;
INSP_ID DEFICIENCY_LENGTH SIDEWALK_LENGTH
---------- ----------------- ---------------
1 0 10
2 2.5 10
3 5 10
4 7.5 10
5 11 10
I can calculate the deficiency_percent
by dividing deficiency_length
by sidewalk_length
:
select
insp_id,
deficiency_length,
sidewalk_length,
deficiency_length / sidewalk_length as deficiency_percent
from
user.sidewalk_insp;
INSP_ID DEFICIENCY_LENGTH SIDEWALK_LENGTH DEFICIENCY_PERCENT
---------- ----------------- --------------- ------------------
1 0 10 0
2 2.5 10 .25
3 5 10 .5
4 7.5 10 .75
5 11 10 1.1
Rather than calculate the deficiency_percent
(a.k.a. 'percent bad'), I want to calculate the condition_percent
(a.k.a. 'percent good'). I can do this by subtracting the percentage from 1
:
select
insp_id,
deficiency_length,
sidewalk_length,
1 - (deficiency_length / sidewalk_length) as condition_percent
from
user.sidewalk_insp;
INSP_ID DEFICIENCY_LENGTH SIDEWALK_LENGTH CONDITION_PERCENT
---------- ----------------- --------------- ------------------
1 0 10 1
2 2.5 10 .75
3 5 10 .5
4 7.5 10 .25
5 11 10 -0.1
Unfortunately, due to a quirk of the data (user input errors), the deficiency_length
is occasionally longer than the sidewalk_length
. While, in theory this is impossible (a sidewalk's deficiency should never be longer than it's length), in reality, it is something that happens that is beyond my control (the application is not customizable).
The result is a negative number in the condition_percent
column (see insp_id #5
) which is not a valid percentage. Instead of outputting a negative number, I think the best way to handle negative percentages would be to output a value of 0
(0%).
What is the simplest way to do this?
Best Answer
Or you could just use
GREATEST
with0
:The above function returns 0 or calculated condition percent, whichever is greater.