I have a table with the following properties
Sales person
Acc Gained
Jobs gained
Rev Gained
jamie
10
32
100,000
John
8
44
120,000
amy
10
38
420,000
Tim
3
12
125,000
...
...
...
...
I want to write an SQL script creating 3 additional columns, ranking "acc gained", "Job gained" and "Rev gained", with the highest value Being rank 1.
However, I do not want draws in any category. If 2 people have the same amount of "acc gained", refer to the highest value based on Jobs gained, and if Jobs gained are the same, refer to "rev gained". it is very unlucky that 2 people would have the exact same rev gained".
Logic as follow:
Acc Rank: check "Acc gained", if same check "Jobs Gained", if same check "rev gained"
Jobs Rank: check "jobs gained", if same check "rev gained"
Rev gained: check "rev gained" (never had a situation where 2 ppl have the same rev)
sample desired output:
Sales person
Acc Gained
Acc rank
Jobs gained
job rank
Rev Gained
rev rank
jamie
10
2
32
3
100,000
4
John
8
3
44
1
120,000
3
amy
10
1
38
2
420,000
1
Tim
3
4
12
4
125,000
2
...
...
...
...
