0

I have this requirement of calculating a custom rank. I need to calculate Annualized % Return for all the 6 companies. I get rank from their return %. Let's consider this as input data. Now I need to calculate Custom Ranking where if another company's return is within a percentage point of Company A, I need to assign all these companies same rank (as shown in the chart below). I have 6 companies which is going to be fixed.

So, in a nutshell my requirement is to find which companies are within a percentage point return of company A. Then convert their rank to a string and concatenate their rank; keep the rest of the ranks the same and assign it to a new variable.

enter image description here

Attached Image is for illustration only.

Dale K
  • 25,246
  • 15
  • 42
  • 71
ITNube
  • 31
  • 5
  • 2
    Can you instead include a sample of the actual input SQL table? You posted a screenshot from Excel, and Excel is not the same thing as SQL. – Tim Biegeleisen Oct 29 '19 at 03:58
  • 1
    How do you define percentage point of return from company A. Do you have specific percentage to consider ? – Venkataraman R Oct 29 '19 at 04:28
  • What if your annualized returns are like 0.5%, 1%, 1.5%, 2%, 2.5%, and so on. What groups do you create? – Gordon Linoff Oct 29 '19 at 11:59
  • @GordonLinoff Requirement is that Customized Rank needs to be calculated based on A's % Return. To answer your question if A's return is 1.5% and other 5 is between (Range is between +1% and -1% of A's return) .5% to 2.5% -> then for all companies customized rank will be 1/2/3/4/5/6 Say B .5% C 1% A 1.5% D 2% E 2.25% F 2.5% So Normal rank is F 1 E 2 D 3 A 4 C 5 B 6 In this scenario --- everyone will have same customized rank 1/2/3/45/6 – ITNube Oct 29 '19 at 12:45
  • @GordonLinoff Other scenario Say B .5% C 1% A 1.5% D 2% E 2.25% F 2.75% So Normal rank is F 1 E 2 D 3 A 4 C 5 B 6 Since F is not in range of A' s +/- 1% --> F keeps it's own rank 1 ---> Rest all will be 2/3/4/5/6 – ITNube Oct 29 '19 at 12:49
  • @VenkataramanR by a percentage point I meant to say in the range of + or - 1% of A's return. – ITNube Oct 29 '19 at 12:50

1 Answers1

2

The trick is to find the dense_rank() based on the absolute value of Difference from A. For difference less than 1.0%, it is treat as 0.

-- Sample Table
declare @company table
(
    Company         char,
    AnnualReturns       decimal(5,1)
)

-- Sample Data
insert into @company 
values ('A', 5.5), ('B', 7.7), ('C', -1.3), ('D', 6.3), ('E', 5.4), ('F', 9.0)

-- The query
; with cte as
(
    select  *,
        [Difference from A] = AnnualReturns - 5.5,
        ActualRank          = row_number() over (order by AnnualReturns desc),
        dr                  = dense_rank() over (order by case when abs(AnnualReturns - 5.5) <= 1.0 
                                                               then 0 
                                                               else abs(AnnualReturns - 5.5) 
                                                               end)
    from    @company
) 
select  Company, AnnualReturns, [Difference from A], ActualRank,
        stuff(RequiredRank, 1, 1, '') as RequiredRank
from    cte c
        cross apply -- concatenate the rank
        (
            select  '/' + convert(varchar(10), ActualRank)
            from    cte x
            where   x.dr    = c.dr
            order by ActualRank
            for xml path('')
        ) rr (RequiredRank)
order by Company
Squirrel
  • 23,507
  • 4
  • 34
  • 32
  • you can easily concatenate the string using str_agg in Azure SQL. https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-ver15 – Venkataraman R Oct 29 '19 at 04:25