1 minus percentage returns negative number

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?

Or you could just use `GREATEST` with `0`:

``````select
insp_id,
deficiency_length,
sidewalk_length,
greatest(0, 1 - (deficiency_length / sidewalk_length)) as condition_percent
from
user.sidewalk_insp;
``````

The above function returns 0 or calculated condition percent, whichever is greater.

This might not be the most elegant way of doing it, but it seems to work:

``````select
insp_id,
deficiency_length,
sidewalk_length,
case when nvl(deficiency_length,0) <= sidewalk_length
then 1 - (deficiency_length / sidewalk_length)
else 0
end condition_percent
from
user.sidewalk_insp;
``````