T-sql: Select most specific match from table

Apologies for the horrible title. I couldn't come up with a better way to briefly describe the problem.

Here's the scenario:

Users can set up service instructions to be attached to a work order based on the order's client, loan type, bank, pricing region, and service. The service instruction is set up with the following parameters:

• Applies to all clients or a specific client

• Applies to all loan types or a specific loan type

• Applies to all banks or a specific bank

• Applies to all pricing regions or a specific pricing region

• Applies to a specific service

A value of 1 in a column in the service_instruction table described below equates to "all".

The table structure (the relevant parts anyway):

order (order_num INT PRIMARY KEY, client_num INT, loan_type INT, 
       bank_num INT, pricing_region INT)

work_order_line(work_order_line_num INT PRIMARY KEY, order_num INT, service_num INT,
                description TEXT)

service_instruction(instruction_num INT PRIMARY KEY, service_num INT,
                    service_description TEXT, client_num INT NULL,
                    bank_num INT NULL, region_num INT, loan_type_cd TINYINT)

I need to write a script which will select the most specific instruction for a service, if one exists. For example:

The user has set up the following service instructions:

• (All clients), (All loan types), (all banks), (all pricing regions), (service A), Instructions: Do work A

• (Client 1), (All loan types), (All banks), (region 3), (service A), Instructions: Do work B

An order which comes in for Client 1, region 3, and service A should have "Do work B" appended.
An order which comes in for Client 2, region 3, and service A should have "Do work A" appended.
An order which comes in for service B should have nothing appended.
And so on.

Currently this is done in code, but circumstances necessitate scripting it. Is there a way to do this that's more elegant (and with better performance perhaps) than the current

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:

• 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

• 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

• You'll probably have to fix the CTE with your rules for specificity/generality

• I tested on 2008R2 - YMMV if you are on an earlier version

StackExchange Database Administrators Q#5044, answer score: 2