How to get result based on multiple column weightage wise sorting

Question

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

I have following input form to take input from user:

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

Result table is as below

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.

Answer 1

Below query working perfect:

ALTER PROCEDURE [dbo].[GetResult] 
    @c1 as decimal(7,2), @c2 as decimal(7,2), @c3 as decimal(7,2), @c4 as decimal(7,2), @c5 as decimal(7,2), 
    @v1 as decimal, @v2 as decimal, @v3 as decimal, @v4 as decimal, @v5 as decimal, 
    @w1 as int, @w2 as int, @w3 as int, @w4 as int, @w5 as int
AS
BEGIN

DECLARE @var1 decimal(4,3) = @v1/100;
DECLARE @d1 decimal(7,3) = @c1 * @var1

DECLARE @var2 decimal(4,3) = @v2/100;
DECLARE @d2 decimal(7,3) = @c2 * @var2;

DECLARE @var3 decimal(4,3) = @v3/100;
DECLARE @d3 decimal(7,3) = @c3 * @var3

DECLARE @var4 decimal(4,3) = @v4/100;
DECLARE @d4 decimal(7,3) = @c4 * @var4

DECLARE @var5 decimal(4,3) = @v5/100;
DECLARE @d5 decimal(7,3) = @c5 * @var5

select * 
from(
    select results.*
        ,   (abs(@c1 - c1val) / @c1 * @w1 +
            abs(@c2 - c2val) / @c2 * @w2 +
            abs(@c3 - c1val) / @c3 * @w3 +
            abs(@c4 - c1val) / @c4 * @w4 +
            abs(@c5 - c1val) / @c5 * @w5 / 5)
            AS relevance 
    from results
    where (c1val between @c1 - @d1 and  @c1 + @d1) or
          (c2val between @c2 - @d2 and  @c2 + @d2) or
          (c3val between @c3 - @d3 and  @c3 + @d3) or
          (c4val between @c4 - @d4 and  @c4 + @d4) or
          (c5val between @c5 - @d5 and  @c5 + @d5)
) t
order by relevance
END

Answer 2

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.