Application areas

Application areas for formulas stored in database fields

Colbert allows to solve problems in SQL that otherwise would have to be solved outside the database for a multiple of cost and completion time.

We have listed a couple of examples below. See how easy problems can now be solved in SQL and realize how difficult, if not impossible, it would be to solve this in SQL without Colbert !

Criteria matching

Find a match between buyers and available cars

A formula can have a result of any type: for instance, a boolean formula Find can define criteria to indicate a person’s interest in a particular second hand car.

When a formula is called in a SQL query, it is compiled once and parameters are bound to expressions found in the context of that query.
Buyer search car by formula
Buyer
Second hand cars
Cars

The challenge:
Show which car matches the search criteria for every buyer.

  • The Buyer table is defined as: create table Buyer(Name string, Find Expr<bool>)
  • No further SQL extensions are needed.
  • The calculated value of Find is used in the where-clause to find a match.
Colbert SQL with a Formula in the where-clause:

Buyer join Cars where Find

Bonus calculation

Calculate the individual annual employee bonus

With a field Bonus of type <Expr>double , the agreed annual employee bonus can easily be calculated.

Employees table with bonus formulas
Employees
Sales figures
Sales

The challenge:
Show the individual annual employee bonus.

Bonus calculation in SQL
Colbert SQL with a Formula in the from-clause:

Name, Year, Bonus from Employees natural join Sales
where Year between 2016 and 2018

Risk analysis

Evaluate risk scores in e.g. insurance or banking industry

To determine risk scores for a population, two formulas are
defined in the Risk table: a boolean risk criterion and an integer score calculation: If the criterion holds, the score contributes to a risk.

Risk criteria and ris scores in SQL
Risk
Fam table
Fam

The challange:
Show the caclulated risk Scores for the matching Crit.

  • The Risk table is defined as:
    Create table Risk (Crit Expr<bool>, Score Expr<int>)
  • As table Fam has no Age field, it is defined in the from-clause as AgeOf BirthDay
  • The operator Code shows the formula itself instead of the calculated value.
Risk calculation in SQL
Colbert SQL with a Formula in the from-clause and a Formula in the where-clause:

Fam.*, AgeOf BirthDate as Age, Code Crit, Code Score, Score
from Fam join Risk on crit

Cohort definition

Divide a population into cohorts based on a formula

Sometimes it is not precise enough to divide a population into cohorts based on field values. Sometimes you want to divide a population into several cohorts by assigning characteristics based on formulas.

With Colbert you can apply these formulas with a SQL join to a population to get the right cohort division.

Fam table
Fam
Cohort definition
FamCohort

The challange:
Show cohorts

  • In this example a person is assigned to exactly one cohort based on the matching formula with the lowest Rank.
    In Colbert this can be done by the First operator.
First found cohort in SQL
Colbert SQL with the First operator and a Formula in the on-clause.

Name, CohortName from Fam
join first (FamCohort order by Rank) on Def