Game with a list of conditions

In this article I will show what and how to do with a list of conditions. I will formulate a small test task based on the AdventureWorks2008R2 base and one of the options for solving it.

Example task:

Calculate the cost of delivery upon 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 of other goods
• Delivery to other cities

On the one hand, it seems that such a task is complex and any change in conditions or tariffs requires the intervention of a programmer (which, ideally, needs to be avoided). In reality, lists of conditions are much more complicated and there are many of them.

First you need to decide on the list of tables, their aliases, and all of them combined 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. A condition for filtering the processed array of records (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 a single 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 problem):

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 the service for a given tariff:

Select <…>
From <Секция FROM>
Cross apply (
   Select id = <КлючУсловия1>,  price = , value = <ФормураРасчета1> where <Условие1>
  Union all
   Select id = <КлючУсловия2>,  price = , value = <ФормураРасчета2> where <Условие2>
  ….
) Services
Where <ОсновноеУсловие>

3. Slightly not according to the proposed task, but you can get the key of the highest 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 CASE always has at least one condition
4. You can combine 1 and 2 points to visually check the conditions.

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

• 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 for condition text and formula text. In extreme cases, you can substitute the constant of the negative (1 <> 1) or positive (1 = 1) conditions instead of the empty condition, and use 0 or NULL instead of the value.
• Always enclose conditions and formulas in parentheses. Brackets are not superfluous.
• Do not forget that the list of conditions may be empty. Practice this situation
• The technique of adding the first element and the next is always slightly different (except for building the CASE).

Now we will combine the conditions, build a dynamic query and execute it (everything except the last row can be executed on any MSSQL base, 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  cross apply () Calc
    where (  )
    group by sh.SalesOrderID, calc.conditionID
end'
set @sql = replace(@template, ''     , @sql)
set @sql = replace(@sql     , ''           , @from)
set @sql = replace(@sql     , '', @basicCondition)
print @sql –- он симпатичный. ))
-- до этого момента код выполнится на любой базе данных
execute( @sql ) -- А вот сам запрос нужно запускать на базе AdventureWorks2008R2
exec #exec_calc ''20071001'', ''20071031'' 

The results of this algorithm:

1. Little time is spent on preparing the request: Analysis of the conditions table, building the request itself. All this is done within very small tables.
2. Most of the time is spent on calculating tariffs according to tables with orders. According to these tables, all tariffs are calculated in one pass.
3. Setting the execution plan of the main request should be based on the main condition.