0

I have the following two columns in a dataset with approximately 50 columns:

One Two
1B   2A
3D   4C
3D   3D

...

Values are from 1 to 5 (from highest to lowest) and from A to F (from highest to lowest). I would need to calculate how many observations are downgraded by 0.5, 1, 2, 3... With downgrade I mean that the value of One is lower than Two. For example:

One Two
1B 2A

I have that One is lower than Two by 1. I am currently using a case when for each of them:

Select * ,
Case when one like ‘1%’ and two like ‘2%’  then ...

from my_table

However it is very time consuming. Do you know how I could do it in an easier way?

V_sqrt
  • 537
  • 8
  • 28
  • Not clear how you calculate the fractional downgrade. Seems you would want to convert each of the columns One and Two to some numeric value or pair of values (probably in a CTE or derived table) and do some simple math. – Fred May 27 '21 at 14:56

1 Answers1

1

Can't you just use string comparisons?

(case where one < two then 'lower' else 'not lower' end)

Or, if you just want to compare the first character (the digit):

(case when left(one, 1) < left(two, 1) then 'lower' else 'not lower' end)

If you specifically want that the value be "1" less, then:

(case when cast(left(one, 1) as int) = cast(left(two, 1) as int) - 1
      then 'lower' else 'not lower'
 end)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The first case it seems not working. I have 4 in One and 2C in Two and it returns not lower. I am going to test also the other cases. – V_sqrt May 27 '21 at 12:11
  • @Val . . . Unless you are using a very strange collation, `'4' > '2'`. Could there be a "hidden" character as the first character, say a space? – Gordon Linoff May 27 '21 at 12:27
  • Yeah, I know. It is quite weird. No, there is no space before he first character. The format is X(4), type Cf – V_sqrt May 27 '21 at 12:34