T-sql: calculate sum of factors when quantity of factors can differ from time to time

Would you please, help me, to develop the algorythm of counting rating of the clients. Initial dataset and desirable result is in the code below. Thank you.

The logic: We have clients and 6 factors (with values 1 or 0 (present or not present)).

We should calculate a rating of the client:

1 (max rate) - client has all the factors

2 - client has factors 1-5 and doesn't have 6th

3 - client has factors 1-4 and doesn't have 5th (factor 6 doesn't matter)

4 - client has factors 1-3 and doesn't have 4th (factors 5-6 don't matter)

5 - client has factors 1-2 and doesn't have 3rd (factors 4-6 don't matter)

6 - client has factor 1 and doesn't have 2nd (factors 3-6 don't matter)

7 - client doesnt have factor 1 (factors 2-6 don't matter)

The key is that number of factors can differ from time to time.

drop table if exists #tmp;

create TABLE #tmp (
[client] [nvarchar] null,
    [factor1] [int] NULL,
    [factor2] [int] NULL,
    [factor3] [int] NULL,
    [factor4] [int] NULL,
    [factor5] [int] NULL,
    [factor6] [int] null,
    [desirable_result] [int] NULL
) 

insert into #tmp (
[client]
    ,[factor1]
    ,[factor2]
    ,[factor3]
    ,[factor4]
    ,[factor5]
    ,[factor6]
    ,[desirable_result]
)
select '1', 1,1,1,1,1,1,1 union all
select '2', 1,1,0,1,1,1,5 union all
select '3', 1,0,1,1,0,1,6 union all
select '4', 1,1,1,1,1,0,2 union all
select '5', 1,1,1,0,0,1,4

This solution works, but only if the num of factors is always equal. The key is that number of factors can differ from time to time.

select *
, "factor1" + "factor2" + "factor3" + "factor4" + "factor5" + "factor6" sum_6
, "factor1" + "factor2" + "factor3" + "factor4" + "factor5" sum_5
, "factor1" + "factor2" + "factor3" + "factor4" sum_4
, "factor1" + "factor2" + "factor3" sum_3
, "factor1" + "factor2" sum_2
, "factor1" sum_1
into #tmp2
from #tmp

select * 
, case when sum_6 = 6 then 1 else 
(case when sum_5 = 5 and sum_6 < 6 then 2 else 
(case when sum_4 = 4 and sum_5 < 5 then 3 else 
(case when sum_3 = 3 and sum_4 < 4 then 4 else 
(case when sum_2 = 2 and sum_3 < 3 then 5 else 
(case when sum_1 = 1 and sum_2 < 2 then 6 else 
7
end)
end) 
end) 
end) 
end) 
end rate
from
#tmp2

Solution

you can use CASE WHEN ... as what scaisEdge has demonstrated.

What i have here is to UNPIVOT the table using CROSS APPLY and then using SUM() with CASE to workout the necessary logic

select  t.client, 
        t.[desirable_result],
        case    when    sum(f.fval) = 6 then 1
                when    sum(f.fval) = 5 
                and     sum(case when f.fno  = 6 then f.fval end) = 0 then 2
                when    sum(case when f.fno <= 4 then f.fval end) = 4 
                and     sum(case when f.fno  = 5 then f.fval end) = 0 then 3
                when    sum(case when f.fno <= 3 then f.fval end) = 3 
                and     sum(case when f.fno  = 4 then f.fval end) = 0 then 4
                when    sum(case when f.fno <= 2 then f.fval end) = 2 
                and     sum(case when f.fno  = 3 then f.fval end) = 0 then 5
                when    sum(case when f.fno  = 1 then f.fval end) = 1 
                and     sum(case when f.fno  = 2 then f.fval end) = 0 then 6
                when    sum(case when f.fno  = 1 then f.fval end) = 0 then 7
                end
from    #tmp t
        cross apply
        (
            values  
            (1, factor1),
            (2, factor2),
            (3, factor3),
            (4, factor4),
            (5, factor5),
            (6, factor6)
        ) f (fno, fval)
group by t.client, t.[desirable_result]
order by t.client