Search code examples
sqlsql-serversql-server-2017

How to get result based on multiple column weightage wise sorting

My question is quite interesting and need help from all sql masters here.

I have following input form to take input from user:

enter image description here

User will enter value, variance and weightage for c1, c2 etc.

Result table is as below

enter image description here

I need to retrieve data from result table based on following conditions:

  1. Value entered by user c1 should be match in c1 column of table with given variance percentage. Eg, value entered is 15000 and % is 10 so match should be 10% +- in table.

  2. Result should be in order of weightage given by user for each column. Max weightage is 5. Eg. Even if C1 value is exact match and c2 value is near match to variance, if weightage of c2 is higher then c2 row should be on top.

I tried below query:

DECLARE @c1 int; set @c1 = 15000;
DECLARE @c2 int; set @c2 = 4;
DECLARE @c3 int; set @c3 = 570;
DECLARE @c4 int; set @c4 = 2000;
DECLARE @c5 int; set @c5 = 450;
select results.*, ((((@c1 - c1val) * 100 / @c1 ) * 6-1) + (((@c2 - c2val) * 100 / @c2) * 6 - 1) + (((@c3 - c3val) * 100 / @c3) * 6-5)+ (((@c4 - c4val) * 100 / @c4) * 6-3)+ (((@c5 - c5val) * 100 / @c5) * 6-3)) AS relevance 
from results
where (c1val <= @c1 - (@c1 * 10 /100) or c1val > = @c1 +  (@c1 * 10 /100)) or 
      (c2val <= @c2 - (@c2 * 25 /100) or c2val > = @c2 + (@c2 * 25 /100)) or
      (c3val <= @c3 - (@c3 * 20 /100) or c3val > = @c3 + (@c3 * 20 /100)) or
      (c4val <= @c4 - (@c4 * 10 /100) or c4val > = @c4 + (@c4 * 10 /100)) or
      (c5val <= @c5 - (@c5 * 15 /100) or c5val > = @c5 + (@c5 * 15 /100))

order by relevance desc

but its not working correct.

Solution

  • First you need decimal arithmetic, declare @c1 etc decimal. Otherwise all expressions are casted to int. Next i guess you need abs(@c1 - c1val) etc in relevance expression. Also my guess is that the relevance is inversely proportional to the deviation.

    Try

    DECLARE @c1 decimal(7,2) = 15000
      , @var1 decimal(4,3) = 10./100;
DECLARE @d1 decimal(7,3) = @c1 * @var1
      , @w1 int = 1;
...
select * 
from(
    select results.*
        , (100 - abs(@c1 - c1val) * 100 / @c1)  * (6 - @w1) 
        +  ...  AS relevance 
    from results
    where (c1val between @c1 - @d1 and  @c1 + @d1)
       AND  ...
) t
order by relevance desc

    Relevance may need a kind of tuning to get a proper balance of weight and deviation.