T-sql – Lookup Table Decisions – Lookup Based On Characters In Specific Positions

My first step would be to rank the service_instruction by specificity/generality - I don't think you have provided enough information to be sure what you are after but assuming that it is based on the number of 1s and ties are broken by: bank_num is more specific than loan_type_cd is more specific than client_num:

select *, row_number() over ( order by case when bank_num=1 then 1 else 0 end+
                                       case when loan_type_cd=1 then 1 else 0 end+
                                       case when client_num=1 then 1 else 0 end,
                                     case when bank_num=1 then 1 else 0 end,
                                     case when loan_type_cd=1 then 1 else 0 end,
                                     case when client_num=1 then 1 else 0 end ) as gen
from service_instruction;

produces:

instruction_num service_num instruction_desc    bank_num    region_num  loan_type_cd    client_num  gen
3   251 Take the photos, yeah   17  96  3   1   1
4   251 Bid for debris removal. 1   471 1   7   2
7   251 Bid for debris removal. Do not perform  1   3   1   1   3

This can then be joined to order and work_order_line and filtered for the row for each work_order_line_num with the lowest generality (gen), perhaps something like this:

with w as (
    select *, row_number() over ( order by case when bank_num=1 then 1 else 0 end+
                                           case when loan_type_cd=1 then 1 else 0 end+
                                           case when client_num=1 then 1 else 0 end,
                                         case when bank_num=1 then 1 else 0 end,
                                         case when loan_type_cd=1 then 1 else 0 end,
                                         case when client_num=1 then 1 else 0 end ) as gen
    from service_instruction )
select *
from( select l.*, instruction_desc, row_number() over ( partition by l.work_order_line_num, 
                                                                     l.order_num, 
                                                                     l.service_num
                                                        order by gen ) as gen_rank
      from work_order_line l join [order] o on(o.order_num=l.order_num)
           join w on( l.service_num=w.service_num 
                      and (o.bank_num=w.bank_num or w.bank_num=1) 
                      and (o.loan_type_cd=w.loan_type_cd or w.loan_type_cd=1)
                      and (o.client_num=w.client_num or w.client_num=1)) ) z
where gen_rank=1

which produces:

work_order_line_num order_num   service_num instruction_desc    gen_rank
20  1   251 Bid for debris removal. Do not perform  1
21  2   251 Bid for debris removal. 1
22  3   251 Take the photos, yeah   1
26  4   251 Bid for debris removal. Do not perform  1

notes:

1. I've assumed you need to partition by work_order_line_num, order_num and service_num but maybe not all are necessary depending on the PK of work_order_line
2. I've ignored the pricing_region complexity you mention but don't specify completely - hopefully you'll be able to work it into the query in a similar manner to the rest
3. You'll probably have to fix the CTE with your rules for specificity/generality
4. I tested on 2008R2 - YMMV if you are on an earlier version

My first question is:

How would you write a query (SELECT) with a similar pattern?

I can see several problems with the UPDATE statement:

• UPDATE on 2 tables (tblTitan t, tblLevel l)
• No FROM clause
• Old JOIN syntax (TableA, TableB VS FROM TableA INNER JOIN TableB ON ...)
• Invalid (missing) expression in the WHERE clause (usage of < and > operators like a BETWEEN operator: A > B AND < C)

A SELECT statement would be like this:

SELECT titanId, titanName
    , t.titanLevel, t.titanStep
    , l.levelNo , l.levelStep
FROM @tblTitan t
INNER JOIN @tblLevel l
    ON t.titanExperience >= l.levelExperienceMin 
    AND t.titanExperience <= l.levelExperienceMax ;

• tblTitan t, tblLevel l is replaced by a proper INNER JOIN clause
• the WHERE clause is replaced by the ON clause in the INNER JOIN
• the invalid BETWEEN like WHERE clause is replace by 2 expresions with < and >

From this valid query, the FROM ... JOIN ... WHERE part can be kept and the SELECT can be replaced by an UPDATE t and a SET clauses.

The UPDATE statement can be writen like this:

UPDATE t
    SET t.titanLevel = l.levelNo
        , t.titanStep = l.levelStep
FROM @tblTitan t
INNER JOIN @tblLevel l
    ON t.titanExperience >= l.levelExperienceMin 
    AND t.titanExperience <= l.levelExperienceMax 
-- WHERE titanId = ...;

Sample data with 3 levels and 3 titans:

DECLARE @tblTitan TABLE (titanId int, titanName varchar(15) , titanExperience int, titanLevel int, titanStep int)
INSERT INTO @tblTitan(titanId, titanName, titanExperience, titanLevel, titanStep) VALUES
    (1, 'Bob', 110, 55, 66)
    , (2, 'Sam', 220, 77, 88)
    , (3, 'Isa', 50, 22, 33)

DECLARE @tblLevel TABLE(levelId int, levelNo int, levelStep int, levelExperienceMin int, levelExperienceMax int)
INSERT INTO @tblLevel(levelId, levelNo, levelStep, levelExperienceMin, levelExperienceMax) VALUES
    (1, 1, 1, 0, 100)
    , (2, 1, 2, 101, 200)
    , (3, 2, 1, 201, 300)

Output of SELECT before UPDATE:

titanId | titanName | titanLevel    | titanStep | levelNo   | levelStep
3       | Isa       | 22            | 33        | 1         | 1
1       | Bob       | 55            | 66        | 1         | 2
2       | Sam       | 77            | 88        | 2         | 1

Output of SELECT after UPDATE:

titanId | titanName | titanLevel    | titanStep | levelNo   | levelStep
3       | Isa       | 1             | 1         | 1         | 1
1       | Bob       | 1             | 2         | 1         | 2
2       | Sam       | 2             | 1         | 2         | 1