Evaluating rules from a table and applying them to data (in TSQL)
-
Hi, I have a situation where I want to rate products according to certain factors that apply to rules that I want to store in a table. This rating needs to be generated in some way and I am hoping to have it done in T-SQL. So the question is how to apply rules stored in one table against data in another table with a view to updating a rating value.
Picture a Producttable:
- ProductID (identity)
- SalesValue, money
- SalesQTY, int
- Rating, int
Picture a Rule table:
- RuleID Int (identity)
- Expression (varchar(512))
- RatingValue (int)
I am really looking for a mechanism that allows me to apply every rule in the Rule table against the product table that updates the Rating field. This mechanisn would allow users to apply rules to the products in order to rate them as need be. If the Rule is true then the product gets that rating.
I hope this isnt too vague, I am really looking for some idea on where to start. I've looked at regular expressions quickly and thought there must be an easier way before I move onto that or CLR.
-
have you got an example of a rule that may be applied?
Tom
Life: it twists and turns like a twisty turny thing
-
Thanks for replying. An example is listed below.
Product
ProductID|SalesValue|SalesQTY|Rating
----------------------------------------------------------------------------
1001|5.00|10|5
1002|6.00|12|4
Rule
RuleID|Expression|RatingValue
----------------------------------------------------------------------------
10000|SalesValue =10|5
10001|SalesValue>5 AND QTY>11|4
What i am thinking is that a process needs to be added that runs the two rules (in Rule table) against the two products in the "product" table. If a rule is found to be true then it updates the Product.Rating field with the Rule.RatingValue that was found to be true.
Of course there are issues about if two rules are true then what to do but I am trying to keep the example simple for now.
Any advice you have would be appreciated. 🙂
-
try this:
CREATE TABLE dbo.[rowrule](
RuleID INT NOT NULL IDENTITY(600000,1) PRIMARY KEY
,SalesValue money NULL
,SalesQTY INT NULL
,SalesValueOperand CHAR(2) NULL CHECK (SalesValueOperand IN ('=','>','<','<>','>=','<='))
,SalesQTYOperand CHAR(2) NULL CHECK (SalesQTYOperand IN ('=','>','<','<>','>=','<='))
,SalesValueIsNull bit NOT NULL DEFAULT (0)
,SalesQTYIsNull bit NOT NULL DEFAULT (0)
,LogicalOperator CHAR(10) NULL CHECK (LogicalOperator = 'AND')
,Rating INT NOT NULL
)
GO
INSERT INTO dbo.rowrule (SalesValue, SalesQTY, SalesValueOperand, SalesQTYOperand, SalesValueIsNull, SalesQTYIsNull, LogicalOperator, Rating) VALUES (NULL, 10, NULL, '=', 0, 0, NULL, 4)
INSERT INTO dbo.rowrule (SalesValue, SalesQTY, SalesValueOperand, SalesQTYOperand, SalesValueIsNull, SalesQTYIsNull, LogicalOperator, Rating) VALUES (5, 11, '>', '>', 0, 0, 'AND', 5)
GO
CREATE FUNCTION dbo.fnCheckRules (
@SalesValue money
,@SalesQTY INT
) RETURNS INT
AS
BEGIN
DECLARE @rules TABLE (SalesValue INT, SalesQTY INT)
-- check to see if SalesValue matches rule
INSERT INTO @Rules (SalesValue)
SELECT
CASE WHEN salesvalueoperand = '=' AND @salesvalue = r.salesvalue THEN r.ruleid
WHEN salesvalueoperand = '>' AND @salesvalue > r.salesvalue THEN r.ruleid
WHEN salesvalueoperand = '<' AND @salesvalue < r.salesvalue THEN r.ruleid
WHEN salesvalueoperand = '>=' AND @salesvalue >= r.salesvalue THEN r.ruleid
WHEN salesvalueoperand = '<=' AND @salesvalue <= r.salesvalue THEN r.ruleid
WHEN salesvalueoperand = '<>' AND @salesvalue <> r.salesvalue THEN r.ruleid
ELSE NULL END AS SalesValue
FROM
dbo.rowrule r
-- check to see if SalesQTY matches rule
UPDATE rl
SET SalesQTY = r.SalesQTY
FROM
@Rules rl
INNER JOIN
(SELECT
CASE WHEN salesqtyoperand = '=' AND @salesqty = r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '>' AND @salesqty > r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '<' AND @salesqty < r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '>=' AND @salesqty >= r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '<=' AND @salesqty <= r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '<>' AND @salesqty <> r.salesqty THEN r.ruleid
ELSE NULL END AS SalesQTY
FROM
dbo.rowrule r) r
ON rl.salesvalue = r.salesqty
INSERT INTO @Rules (SalesQTY)
SELECT
CASE WHEN salesqtyoperand = '=' AND @salesqty = r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '>' AND @salesqty > r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '<' AND @salesqty < r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '>=' AND @salesqty >= r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '<=' AND @salesqty <= r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '<>' AND @salesqty <> r.salesqty THEN r.ruleid
ELSE NULL END AS SalesQTY
FROM
dbo.rowrule r
LEFT OUTER JOIN
@Rules rl
ON rl.SalesValue = CASE WHEN salesqtyoperand = '=' AND @salesqty = r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '>' AND @salesqty > r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '<' AND @salesqty < r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '>=' AND @salesqty >= r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '<=' AND @salesqty <= r.salesqty THEN r.ruleid
WHEN salesqtyoperand = '<>' AND @salesqty <> r.salesqty THEN r.ruleid
ELSE NULL END
WHERE
rl.SalesValue IS NULL
-- get the largest rating
DECLARE @rtn INT
SET @rtn = (SELECT
MAX(CASE WHEN rr.logicaloperator = 'AND' AND r.salesvalue IS NOT NULL AND r.salesqty IS NOT NULL THEN rr.rating
WHEN rr.logicaloperator = 'AND' AND (r.salesvalue IS NULL OR r.salesqty IS NULL) THEN NULL
WHEN r.salesvalue IS NOT NULL OR r.salesqty IS NOT NULL THEN rr.rating
ELSE NULL END) AS rating
FROM
dbo.rowrule rr
INNER JOIN
@rules r
ON rr.ruleid = r.salesvalue
OR rr.ruleid = r.salesqty)
RETURN @rtn
END
GO
SELECT
*
,dbo.fnCheckRules(salesvalue, salesqty)
FROM
dbo.product
Life: it twists and turns like a twisty turny thing
-
Or, if you have very complicated and validated expressions (eg QTY needs to be SalesQTY), you could try dynamic SQL. eg:
-- *** Test Data ***CREATE TABLE #Products
(
    ProductID int NOT NULL
    ,SalesValue decimal(10,2) NOT NULL
    ,SalesQTY int NOT NULL
    ,Rating int NULL
)
CREATE TABLE #Rules
(
    RuleID int NOT NULL
    ,Expression varchar(255) COLLATE DATABASE_DEFAULT NOT NULL
    ,RatingValue int NOT NULL
)
INSERT INTO #Products
SELECT 1001, 5.0, 10, NULL UNION ALL
SELECT 1002, 6.0, 12, NULL
INSERT INTO #Rules
SELECT 10000, 'SalesValue = 10', 5 UNION ALL
SELECT 10001, 'SalesValue > 5 AND SalesQTY > 11', 4 -- QTY is not a valid expression
-- *** Test Data ***
DECLARE @SQLString varchar(1000)
DECLARE Rating_Cursor CURSOR LOCAL FAST_FORWARD
FOR
    SELECT 'UPDATE #Products '
        + 'SET Rating = ' + CAST(R.RatingValue as varchar(20)) + ' '
        + 'WHERE ' + R.Expression + ' '
            -- Maximum Rating according to all rules
        +    'AND ISNULL(Rating, 0) < ' + CAST(R.RatingValue as varchar(20))
    FROM #Rules R
OPEN Rating_Cursor
FETCH NEXT FROM Rating_Cursor INTO @SQLString
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC (@SQLString)
    FETCH NEXT FROM Rating_Cursor INTO @SQLString
END
CLOSE Rating_Cursor
DEALLOCATE Rating_Cursor
SELECT *
FROM #Products
-
Thanks to all who answered my call for help. Cheers. 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply