Game with a list of conditions

In this article I will show how and what can be done with the list of conditions. I will formulate a small test problem based on the AdventureWorks2008R2 base and one of its solutions.

Example task:

Calculate the cost of delivery on the fact of the following conditions (a common task for logistics companies).
')
List of conditions:

• Delivery to Berlin and Bonn bikes
• Delivery to Berlin and Bonn other goods
• Delivery to other cities

On the one hand, it seems that such a task is difficult and any change in conditions or tariffs requires the intervention of a programmer (which, ideally, should be avoided). In reality, the lists of conditions are much more intricate and a lot of them.

First you need to decide on the list of tables, their aliases and all of them to join into one join.

declare @from varchar(1000) = ' sales.SalesOrderHeader sh with(nolock) join sales.SalesOrderDetail sd with(nolock) on sh.SalesOrderID = sd.SalesOrderID join Production.Product pp with(nolock) on sd.ProductID = pp.ProductID join Production.ProductModel ppm with(nolock) on pp.ProductModelID = ppm.ProductModelID join Production.ProductSubcategory pps with(nolock) on pp.ProductSubcategoryID = pps.ProductSubcategoryID join Production.ProductCategory ppc with(nolock) on pps.ProductCategoryID = ppc.ProductCategoryID join sales.Customer sc with(nolock) on sh.CustomerID = sc.CustomerID join person.[Address] pa with(nolock) on sh.ShipToAddressID = pa.AddressID ' 

We have two types of conditions:

1. The condition for filtering the processed record array (the Main Condition):

 declare @basicCondition varchar(1000) = ' sh.ShipDate between @begDate and @endDate and sh.[Status] = 5' 

2. A set of conditions, each of which corresponds to one tariff (Condition1, ..., Condition3):

 if OBJECT_ID('tempdb..#Conditions') is not null drop table #Conditions create table #Conditions ( ConditionID int identity(1,1) primary key, Name varchar(100), [Text] varchar(200), [Value] varchar(200) ) insert #Conditions(Name, [Text], [Value]) select '     ', 'pa.city in (''Berlin'', ''Bonn'') and ppc.Name in (''Byke'')' , 'sd.OrderQty * pp.Weight' insert #Conditions(Name, [Text], [Value]) select '      ', 'pa.city in (''Berlin'', ''Bonn'') and ppc.Name not in (''Byke'')', 'sd.OrderQty * pp.Weight' insert #Conditions(Name, [Text], [Value]) select '   ', 'pa.city not in (''Berlin'', ''Bonn'')', 'sd.OrderQty * pp.Weight' 

Having a set of conditions you can do the following:

1. Check the list for correct conditions (one record - one condition for the formulated task):

 select <>, Errors = iif(<1>,<1>,'') + ', ' + iif(<2>,<2>,'') +… from < FROM> where ( <> ) and ( 1 <> iif(<1>,1,0) + iif(<2>,1,0)+… ) 

2. Get the cost of services for a given tariff:

 Select <…> From < FROM> Cross apply ( Select id = <1>, price = <Price1>, value = <1> where <1> Union all Select id = <2>, price = <Price2>, value = <2> where <2> …. ) Services Where <> 

3. A bit not on the proposed task, but you can get the key of the most priority condition for the current record, if you sort the conditions by priority in the reverse order:

 Select service = case When <1> then <1> When <2> then <2> When <3> then <3> … When 1=1 then null End, < > From < FROM> Where <> 

Ps. Pay attention to the condition “when 1 = 1 then null '- I specifically added this condition, so that there would always be at least one condition in CASE
4. You can combine 1 and 2 points for a visual check of conditions.

As you can see, we have a fairly regular query structure, which is easily constructed dynamically. But when building and using such queries, consider the following:

• Security - the user should not have the right to edit the text of the conditions and the text of the values. In the next article I will talk about the user tool for building a query.
• When building a dynamic query, check the availability of condition text and formula text. In the extreme case, instead of an empty condition, you can substitute the constant of a negative (1 <> 1) or positive (1 = 1) condition, and use 0 or NULL instead of a value.
• Always enclose the terms and formulas in brackets. Brackets are not superfluous.
• Do not forget that the list of conditions may be empty. Handle this situation
• The method of adding the first element and the following is always slightly different (except for the construction of CASE).

Now let's combine the conditions, build a dynamic query and execute it (all but the last line can be executed on any MSSQL database, I tested for 2008):

 declare @sql varchar(max) select @sql = case when @sql is null then '' else @sql + char(10) + ' union all '+char(10) end --   SELECT- UNION ALL   + ' select ConditionID = '+convert( varchar(10), ConditionID )+', [Value] = ('+chk.Value+') where ' + chk.Condition from #Conditions outer apply ( select -- -   ,   [Condition] = case when [text] <> '' then [text] else '1<>1' end , [Value] = case when [Value] <> '' then [Value] else 'null' end ) chk If @sql is null set @sql = ' select ConditionID = null, [Value] = null where 1<>1 ' drop table #Conditions --      declare @template varchar(max) = ' create procedure #exec_calc (@begDate datetime, @endDate datetime ) as begin select sh.SalesOrderID, calc.ConditionID, Value = sum( Calc.Value) from <FROM> cross apply (<CONDITIONS>) Calc where ( <BASIC_CONDITIONS> ) group by sh.SalesOrderID, calc.conditionID end' set @sql = replace(@template, '<CONDITIONS>' , @sql) set @sql = replace(@sql , '<FROM>' , @from) set @sql = replace(@sql , '<BASIC_CONDITIONS>', @basicCondition) print @sql –-  . )) --          execute( @sql ) --         AdventureWorks2008R2 exec #exec_calc ''20071001'', ''20071031'' 

The results of this algorithm:

1. Minor time is spent on preparing the query: Analyzing the condition table, building the query itself. All this is done within very small tables.
2. The main time is spent on the calculation of tariffs on the tables with orders. According to these tables, all tariffs are calculated for one pass.
3. Setting the execution plan for the main query should be based on the main condition.