Query Execution with rules

• vijaykumar587

  SSC-Addicted

  Points: 444

  More actions

  July 24, 2015 at 1:15 pm

  #302794

  Hi,

  I need suggestion or solution for my problem.

  I need to implement one logic similar to rule engine. Below is the example.

  Can you please suggest me how to execute all queries in second table order?? what is the best way to implements this

  ----------------------------------

  declare @tblRules AS Table (RuleNo INT, RuleDesc NVARCHAR(500), RuleQuery NVARCHAR(MAX), QueryExecutionInterval NVARCHAR(50))

  declare @tblRuleResults AS Table (RuleResultID INT, RuleNo INT, ExecuteTime DateTime, NextExecutionTime DateTime, Result NVARCHAR(10))

  INSERT INTO @tblRules VALUES ('1','Fail - 2 times within 1 Hour','XXX','Every 15 Minutes')

  INSERT INTO @tblRules VALUES ('2','Fail- 2 times within 2 Hour','YYY','Every 30 Minutes')

  -------------------

  INSERT INTO @tblRuleResults VALUES(1,1,'2015/07/24 09:00:00','2015/07/24 09:15:00','Pass')

  INSERT INTO @tblRuleResults VALUES(2,2,'2015/07/24 09:00:00','2015/07/24 09:30:00','Pass')

  INSERT INTO @tblRuleResults VALUES(3,1,'2015/07/24 09:15:00','2015/07/24 09:30:00','Pass')

  INSERT INTO @tblRuleResults VALUES(4,2,'2015/07/24 09:30:00','2015/07/24 10:00:00','Pass')

  INSERT INTO @tblRuleResults VALUES(5,1,'2015/07/24 09:30:00','2015/07/24 09:45:00','Fail')

  INSERT INTO @tblRuleResults VALUES(6,2,'2015/07/24 10:00:00','2015/07/24 10:30:00','Pass')

  INSERT INTO @tblRuleResults VALUES(7,1,'2015/07/24 09:45:00','2015/07/24 10:00:00','Pass')

  SELECT * FROM @tblRules

  SELECT * FROM @tblRuleResults

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  July 24, 2015 at 1:23 pm

  #1814902

  Could you elaborate more on what do you need? I'm not sure what you're trying to do. If you want to apply rules, you might need to change your table definitions to avoid strings and keep actual values needed in calculations.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Steve Jones - SSC Editor

  SSC Guru

  Points: 734418

  More actions

  July 24, 2015 at 4:27 pm

  #1814933

  Are you saying you want the inserts to run in a certain order? Or something else? As Luis mentioned, it's not clear.

  Rather than assuming we understand your code, or your statement, be clear. Tell us what the table is used for, what the code should do, or what should happen, and reference table names. Don't say first or second table, specifically state TblRuleResults if you mean that one.

  My first inclination is to say add an ordering column in each table. I might also add some type of category, so I could have different process flows.