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 mess of IF..ELSE's and ISNULL's I'm working on?
EDIT: Here's some sample data.
instruction_num | service_num | instruction_desc | bank_num | region_num | loan_type_cd | client_num 3 251 'Take the photos, yeah' 17 96 3 1 4 251 'Bid for debris removal.' 1 471 1 7 7 251 'Bid for debris removal. 1 3 1 1 Do not perform'
order_num | client_num | loan_type | bank_num | pricing_region 1 3 1 1 3 2 7 3 1 471 3 2 3 17 96 4 5 2 6 17
work_order_line_num | order_num | service_num | description 20 1 251 NULL 21 2 251 NULL 22 3 251 NULL 26 4 251 NULL
The end result would be:
work_order_line_num | order_num | service_num | description 20 1 251 'Bid for debris removal. Do not perform.' 21 2 251 'Bid for debris removal.' 22 3 251 'Take the photos, yeah' 26 4 251 'Bid for debris removal. Do not perform.'
A value of 1 for the client_num, bank_num, or loan_type_cd in service_instruction means it applies to all clients, banks, or loan types, respectively. To further complicate things, the pricing_region in service_instruction is dependent upon the client. For example, when the client_num is 1 ("all clients"), a region_num of 3 indicates "all regions." When the client_num is 3, region_num 39 is "all regions". I'm not really looking for someone to write out the script for me, more a pointer in the right conceptual direction, so that complication can probably be ignored for now.
Here's what I've been working on to solve this, edited somewhat for brevity. Yes, there's a cursor in it. Yes, it's going to be put in a production environment. No, I'm not happy about it. Which is why I came here!
--Table var. containing orders we need to update DECLARE @order_numbers TABLE (order_num INT PRIMARY KEY , client_num SMALLINT NOT NULL , order_type_cd TINYINT NOT NULL , loan_type_cd TINYINT NOT NULL , bank_num TINYINT NOT NULL , pricing_region INT NOT NULL , service_num INT NOT NULL , service_instruction_num SMALLINT NULL); --Table containing the match data DECLARE @instruction_matches TABLE (instruction_num SMALLINT PRIMARY KEY , client_match TINYINT , loan_type_match BIT , bank_match BIT , region_match BIT , total_matches TINYINT); DECLARE @best_instruction SMALLINT , @order_num INT , @client_num SMALLINT , @loan_type_cd TINYINT , @bank_num TINYINT , @region_num INT , @service_num INT; --Get the orders to update INSERT INTO @order_numbers (order_num , client_num , order_type_cd , loan_type_cd , bank_num , pricing_region , service_num) SELECT o.order_num, oc.client_num, o.order_type_cd, oc.loan_type_cd, oc.bank_num, oc.pricing_region, wol.service_num FROM [order] o inner join order_context oc ON o.context_num = oc.context_num inner join work_order_line wol ON o.order_num = wol.order_num WHERE o.status_cd = 1 AND o.exchange_status_cd = 2 AND o.received_from_hub_ind = 1 AND o.viewed_dt IS NULL DECLARE o_cursor CURSOR LOCAL FOR SELECT order_num FROM @order_numbers; OPEN o_cursor; FETCH NEXT FROM o_cursor INTO @order_num; WHILE @@FETCH_STATUS = 0 BEGIN SET @service_num = (SELECT service_num FROM @order_numbers WHERE order_num = @order_num); SET @client_num = (SELECT client_num FROM @order_numbers WHERE order_num = @order_num); SET @loan_type_cd = (SELECT loan_type_cd FROM @order_numbers WHERE order_num = @order_num); SET @bank_num = (SELECT bank_num FROM @order_numbers WHERE order_num = @order_num); SET @region_num = (SELECT pricing_region FROM @order_numbers WHERE order_num = @order_num); INSERT INTO @instruction_matches (instruction_num, client_match, loan_type_match, bank_match, region_match) SELECT instruction_num , CASE WHEN client_num = @client_num OR client_num = 1 THEN 1 ELSE 0 END , CASE WHEN loan_type_cd = @loan_type_cd OR loan_type_cd = 1 THEN 1 ELSE 0 END , CASE WHEN bank_num = @bank_num OR bank_num = 1 THEN 1 ELSE 0 END , CASE -- region_num = 4 means INSPECTIONS ONLY. will need to be re-written for pres. WHEN region_num = @region_num OR (client_num != 1 AND region_num = (SELECT region_num FROM region WHERE client_num = @client_num AND region_type_cd = 2 AND order_item_type_cd = 2 AND region_id = 1) ) THEN 2 WHEN client_num = 1 AND region_num = 4 THEN 1 ELSE 0 END FROM service_instruction WHERE service_num = @service_num; UPDATE @instruction_matches SET total_matches = (client_match + loan_type_match + bank_match + region_match); SET @best_instruction = (SELECT TOP(1) instruction_num FROM @instruction_matches ORDER BY total_matches DESC); UPDATE @order_numbers SET service_instruction_num = @best_instruction WHERE order_num = @order_num; FETCH NEXT FROM o_cursor INTO @order_num; END;