October 23, 2018 at 5:34 am
This may be a fool's errand, but I have to try... I have a CASE in my code that checks a number of scenarios before it settles on an answer. I'd like to change it to something more data driven, but I'm not sure if I can get it to work. Thoughts would be appreciated.
Here's a scrubbed version of the code... It basically decides which state it's going to use, depending on a number of scenarios.
DECLARE @ConversionDate DATE = '02/01/2014';
SELECT CASE WHEN CONVERT(DATE,t1.SaleDate) >= @ConversionDate THEN
(CASE WHEN t1.Column1 = 'ValueA' AND t1.Column2 IN ('Pots','Pans') THEN t1.StoreState
WHEN t1.Column1 = 'ValueB' AND t1.Column2 IN ('Pots','Pans') AND t1.OrderType <> 'Credit' AND t1.SaleDate >= '08/01/2017' THEN 'FL'
/* Order type Credit did not exist before 08/01/2017, so does not need to be checked against the original conversion date */
WHEN t1.Column1 = 'ValueB' AND ISNULL(t3.Column8,0) = 0 AND t1.StoreState NOT IN ('OR','NY') THEN ISNULL(t1.WarehouseState,t1.StoreState)
WHEN t1.Column1 = 'ValueB' AND ISNULL(t3.Column8,0) = 0 AND t1.StoreState IN ('OR','NY') THEN 'FL'
WHEN t1.Column1 = 'ValueB' AND ISNULL(t3.Column8,0) = 1 AND t1.OrderType = 'Credit' AND t1.StoreState <> 'CT' THEN ISNULL(t1.WarehouseState,t1.StoreState)
....
END
Every time I turn around, the business wants me to add another rule to this state code, which means I have to change code and we have to test it each time it changes. What I'm trying to do is make this whole process simpler. If I could put the information in a table and use the table to produce the state, we wouldn't have to spend 2-3 months every time a rule changed.
I'm not quite sure where to start with this. Am I creating a flat table that contains every possible combination for the columns and state names? Am I using a text column to contain the rule and match on that (bad idea, I think)? Or is there an easier way to do this that I'm missing?
Any thoughts would be appreciated. I don't need code per say. Just brainstorming thoughts, if you don't mind.
October 23, 2018 at 7:02 am
YES, you need to abstract the logic to make it easier to understand, maintain and test.
My first reaction would be to move the logic to a function as this can be unit tested independently of the main code and promotes DRY code. Performance may be an issue if there are a huge number of rows in the dataset.
You could also build a reference table for all the cross-joined combinations of t1.Column1, t1.Column2, t1.OrderType, t1.SaleDate(from), t1.saleDate (to), t1.StoreState, t1.WarehouseState. and get the business to maintain the return value whenever a new combination occurs; most likely when a new store is opened in a new state or a new Column2 category is introduced. This would require some alerts and maintenance so that the business can feed themselves on maintaining this table.
This is only a viable option if the number and data type of the parameters is unlikely to change, otherwise you will continue to touch the main code as well. If the number or nature of the rules keep changing you will be continually updating the reference table and the code to support the user interface.
October 23, 2018 at 8:17 am
I did exactly that at a previous job. We had to send out letters for an insurance company and the content of the letter depended on the type of insurance policy, the state where the insurance policy was issued, and the date when the insurance policy was issued. There may have been other factors, too, but it's been a long time. We had to do this for several different clauses and the conditions weren't always the same for each clause. It was a huge mess. I think it was over 1000 lines of code just for the various case expressions. These conditions had to be updated every time we sent out a new mailing (every three months), because there were always new policies being issued and new laws being passed which affected the language that needed to be included.
I reworked this into a table, which made it much easier to produce the letters in the future. I did use NULL values to indicate values not otherwise specified and then did a subquery with a TOP 1. (This was before I learned about CROSS/OUTER APPLY.) You do have to be very careful that you get your ORDER BY clause correct. It's also complicated if some of your columns can be NULL.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 24, 2018 at 7:05 am
So basically I am looking at a table to store all the possible combinations in.
I want to avoid a function if possible. I'd have to call it inside a stored procedure that processes ITD records (from 2007 on), which I think would hit performance even harder than the code I already have.
Drew, when you did your table, was it set up with values (the stuff on the right side of the equal signs) that you just used to JOIN to in the ON clause? It sounds like your situation is very similar to mine.
October 24, 2018 at 8:46 am
Brandie Tarvin - Wednesday, October 24, 2018 7:05 AMSo basically I am looking at a table to store all the possible combinations in.I want to avoid a function if possible. I'd have to call it inside a stored procedure that processes ITD records (from 2007 on), which I think would hit performance even harder than the code I already have.
Drew, when you did your table, was it set up with values (the stuff on the right side of the equal signs) that you just used to JOIN to in the ON clause? It sounds like your situation is very similar to mine.
CREATE TABLE #Data
(
Column1 VARCHAR(10),
Column2 VARCHAR(15),
Column8 BIT,
OrderType CHAR(6),
WarehouseState CHAR(2),
StoreState CHAR(2)
)
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 25, 2018 at 3:33 pm
Brandie Tarvin - Tuesday, October 23, 2018 5:34 AMAny thoughts would be appreciated. I don't need code per say. Just brainstorming thoughts, if you don't mind.
You might want to look at "decision tables" and even find a package. This is a technique that's been used for decades, but fell out of fashion at some point. You can find some articles on it in Google and at various websites. Basically, it's a spreadsheet for logic. Part of the grid has whether or not the rules are true, false or we don't care. Another part of the grid has the actions to be taken, and the rest of the spreadsheet supports information about the likelihood of a rule being true, the execution order of the at actions and other things.
You fill out the grid. You push the button. The package automatically tells you what rules are missing and if there are contradictions. When the grid is both complete and logically consistent, you push another button and it automatically generates optimized code in whatever your target language is. This is usually if-then-else statements. In assorted procedural programming languages. If rules change or other conditions are added, all you do is change the grid and push the buttons again. You're guaranteed the code you use is completely optimized. My favorite tool back in the day was called Logic Gem, but there are others out there.
Please post DDL and follow ANSI/ISO standards when asking for help.
October 25, 2018 at 3:52 pm
I think a well-written function might still be the best choice. The logic could get very complex to implement trying to use a table structure. If necessary for performance, make the function ILTV.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 26, 2018 at 5:16 am
I'll look into decision tables. Thank you for the suggestion, Joe.
Scott, I appreciate your input, but I want to make a non-code based solution if possible. The reason for this is it takes us 3 months to update and test code changes. Whether I leave the code in the proc or make a function, that is still 3 months of coding and testing before we can release a change to production. And if there's a new change coming right after the one that's being testing, I can't even start coding until the "current" change is in production. Sometimes that means 5-6 months before the new change can even start being tested.
If I can do this as a data driven process, then we don't have to spend an entire quarter working on this. We can just pop it into the tables within a day or two, do a quick test (like a week's worth of testing) in non-prod to verify the new update works, then go live in production when our BU needs it live.
In this specific case, they wanted to go live October 1st, but didn't tell us until the week before that they were changing state rules. Then they wanted to go live November 1st when we told them we couldn't meet their original deadline. Now they're unhappy because we can't accommodate them until first quarter next year. And we know now that more rule changes are coming down the pipe for other items. It would make life much easier if we could do this as data driven instead of code. Not just for me and the QA team, but for the business as well.
October 26, 2018 at 9:13 am
Brandie Tarvin - Friday, October 26, 2018 5:16 AMI'll look into decision tables. Thank you for the suggestion, Joe.Scott, I appreciate your input, but I want to make a non-code based solution if possible. The reason for this is it takes us 3 months to update and test code changes. Whether I leave the code in the proc or make a function, that is still 3 months of coding and testing before we can release a change to production. And if there's a new change coming right after the one that's being testing, I can't even start coding until the "current" change is in production. Sometimes that means 5-6 months before the new change can even start being tested.
If I can do this as a data driven process, then we don't have to spend an entire quarter working on this. We can just pop it into the tables within a day or two, do a quick test (like a week's worth of testing) in non-prod to verify the new update works, then go live in production when our BU needs it live.
In this specific case, they wanted to go live October 1st, but didn't tell us until the week before that they were changing state rules. Then they wanted to go live November 1st when we told them we couldn't meet their original deadline. Now they're unhappy because we can't accommodate them until first quarter next year. And we know now that more rule changes are coming down the pipe for other items. It would make life much easier if we could do this as data driven instead of code. Not just for me and the QA team, but for the business as well.
I understand. But it seems to me the real problem is that a simple code change takes 3+ months to implement.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 26, 2018 at 11:06 am
Brandie Tarvin - Friday, October 26, 2018 5:16 AMI'll look into decision tables. Thank you for the suggestion, Joe.Scott, I appreciate your input, but I want to make a non-code based solution if possible. The reason for this is it takes us 3 months to update and test code changes. Whether I leave the code in the proc or make a function, that is still 3 months of coding and testing before we can release a change to production. And if there's a new change coming right after the one that's being testing, I can't even start coding until the "current" change is in production. Sometimes that means 5-6 months before the new change can even start being tested.
If I can do this as a data driven process, then we don't have to spend an entire quarter working on this. We can just pop it into the tables within a day or two, do a quick test (like a week's worth of testing) in non-prod to verify the new update works, then go live in production when our BU needs it live.
In this specific case, they wanted to go live October 1st, but didn't tell us until the week before that they were changing state rules. Then they wanted to go live November 1st when we told them we couldn't meet their original deadline. Now they're unhappy because we can't accommodate them until first quarter next year. And we know now that more rule changes are coming down the pipe for other items. It would make life much easier if we could do this as data driven instead of code. Not just for me and the QA team, but for the business as well.
I like the idea of decision tables. They are still around but I think some of it morphed to some the rules engine type of approaches like Workflow engines, business rules engines.
Purchasing a solution with those types of rules engine...those are designed similar to what Joe described. The change was in how it was implemented where you have the rules in some other component, software package that the business users could use to modify and then the application accesses those rules or the logic, whatever using APIs the vendor exposes. That''s how the ones I've seen have worked, could be other approaches. But they allow changes by the users without changes to the code. Integrating those into your current app is where the level of effort would be but it may be something the business would be interested in purchasing. And it moves all of the mess of it back to the business instead of on you and your team.
Sue
November 5, 2018 at 6:14 am
Sue_H - Friday, October 26, 2018 11:06 AMI like the idea of decision tables. They are still around but I think some of it morphed to some the rules engine type of approaches like Workflow engines, business rules engines.
Purchasing a solution with those types of rules engine...those are designed similar to what Joe described. The change was in how it was implemented where you have the rules in some other component, software package that the business users could use to modify and then the application accesses those rules or the logic, whatever using APIs the vendor exposes. That''s how the ones I've seen have worked, could be other approaches. But they allow changes by the users without changes to the code. Integrating those into your current app is where the level of effort would be but it may be something the business would be interested in purchasing. And it moves all of the mess of it back to the business instead of on you and your team.Sue
We're not interested in purchasing a rules engine. For one, it would take us a year to look into the options and test them, let alone get budgeting money to pay for this. If we're lucky, it would only be a year. I need this now and there's no reason to purchase something if I can actually build one to fit our needs.
November 5, 2018 at 6:19 am
ScottPletcher - Friday, October 26, 2018 9:13 AMBut it seems to me the real problem is that a simple code change takes 3+ months to implement.
That's why I'm flipping to data-driven tables whenever I can. @=)
And, unfortunately, this isn't a simple code change. It's a massive Dragon-Poker-Rules type project that we're trying to convert over a more sensical business rules solution bit by bit as we get time. Every time something changes in the business on this project, it's a new "scenario" that needs to be coded for, which means the possibilities of breaking the whole thing increase exponentially because the original business users decided individual scenarios were more important than comprehensive business rules.
Not that I can complain much. I'm the one who built the whole process to their specifications. Now I get to revamp it and needle them about just starting from scratch to make it better than the massive novella's worth of code that it is.
November 5, 2018 at 3:36 pm
Brandie Tarvin - Monday, November 5, 2018 6:19 AMScottPletcher - Friday, October 26, 2018 9:13 AMBut it seems to me the real problem is that a simple code change takes 3+ months to implement.That's why I'm flipping to data-driven tables whenever I can. @=)
And, unfortunately, this isn't a simple code change. It's a massive Dragon-Poker-Rules type project that we're trying to convert over a more sensical business rules solution bit by bit as we get time. Every time something changes in the business on this project, it's a new "scenario" that needs to be coded for, which means the possibilities of breaking the whole thing increase exponentially because the original business users decided individual scenarios were more important than comprehensive business rules.
Not that I can complain much. I'm the one who built the whole process to their specifications. Now I get to revamp it and needle them about just starting from scratch to make it better than the massive novella's worth of code that it is.
Does this mean each scenario needs to be tested to determine which result to use?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply