# Is there a algorithm to resolve credit rating?

#### Tags: arrays, java, plsql, sql

I need to implement a logic in either Java OR SQL for the following.

There are credit ratings from numbers 1 to 11. And corresponding to each rating say x, I have 3 grades, effectively :x+, x and x-. Difference between each grade is termed as a step. For example, 2 and 2- is single(1) step,2+ and 2- is double(2) step, and 2- and 3+ is again a single(1) step.

My objective is to programmatically find out the number of steps between two credit rating values, until the difference in number of steps is 3.

I tried to solve this problem using conditional (if-else/case) statements, but a deeper analysis led to the conclusion that this would require about 50 odd such statements, which is not a very efficient thing to do, at least in terms of code layout.

Is there any other way to get the number of steps? Perhaps using a temporary data structure, and then using that for lookup?

Note: I can have the two credit rating values pulled into Java Code from DB and then compute number of steps. Alternatively, I need to create a database object(like procedure, function) and then have the number of steps computed that way, such that my Java code can directly consume it.

I would just assign a numeric value to each rating and have a function that subtracts one from the other.

```create or replace package rating
as
function diff(r1 varchar2, r2 varchar2)
return integer
deterministic
parallel_enable;
end rating;
/
```
```create or replace package body rating
as
type rating_tab is table of simple_integer index by varchar2(3);
ratings rating_tab;

function diff(r1 varchar2, r2 varchar2)
return integer
deterministic
parallel_enable
is
pragma udf;
begin
return ratings(r2) - ratings(r1);
end diff;

begin
ratings('1+')  := 1;
ratings('1+')  := 2;
ratings('1')   := 3;
ratings('1-')  := 4;
ratings('2+')  := 5;
ratings('2')   := 6;
ratings('2-')  := 7;
ratings('3+')  := 8;
ratings('3')   := 9;
ratings('3-')  := 10;
ratings('4+')  := 11;
ratings('4')   := 12;
ratings('4-')  := 14;
ratings('5+')  := 15;
ratings('5')   := 16;
ratings('5-')  := 17;
ratings('6+')  := 18;
ratings('6')   := 19;
ratings('6-')  := 20;
ratings('7+')  := 21;
ratings('7')   := 22;
ratings('7-')  := 23;
ratings('8+')  := 24;
ratings('8')   := 25;
ratings('8-')  := 26;
ratings('9+')  := 27;
ratings('9')   := 28;
ratings('9-')  := 29;
ratings('10+') := 30;
ratings('10')  := 31;
ratings('10-') := 32;
ratings('11+') := 33;
ratings('11')  := 34;
ratings('11-') := 35;
end rating;
/
```
```select rating.diff('1-', '3+') as steps from dual;

STEPS
---------
4
```

I am not sure whether a PL/SQL function is acceptable though, since you started by saying you wanted to use either Java or SQL.

Source: stackoverflow