Search code examples
sqlteradata-sql-assistantteradatasql

Hierarchical comparison

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?

Solution

  • 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)