1. Analyze the entire history of past transactions
   
2. Perform a complex query requiring a join between entities
   
3. Match on multiple fields and conditions

1. You require complex transaction lookups
   
1. Ex: you cannot call QueryTransactionSingle() because the match has to be done on multiple fields
2. You must examine the history of transactions
   
1. Ex: you cannot call QueryTransactionSingle() because you want to specify a sorting order

1. Use Settings > Connections > OData API to explore the API
   
2. You can also connect using BI tools such as Power BI to explore the API
   

1. CalculationCommissions
   
2. CalculationCredits
3. Transactions

1. Users
   
2. Teams
   
3. Aliases
   
4. Etc.

Here is the portion of the entity schema which you will use most often (please refer to this KB for a short description):

 

1. Related Calculation > Plan
   
2. Related CalculationCredit > Transaction

1. For performance, remove joins you do not need
2. Consider restricting to estimated vs. actual commissions
   
3. Ignore commissions from calculations later than the current calculation (otherwise future calculations could affect past ones)
   
4. Exclude records outside of the current workspace
   
5. Exclude recently deleted records

1. We have CC.IsDeleted = 0 but also C.IsDeleted = 0. When a calculation is deleted, related entities such as CalculationCommissions or CalculationCredits may not be deleted immediately. For this reason, we recommend also checking if the calculation has been deleted. We don't need to check ALL joined entities - only the main entity (CalculationCommission) and the related Calculation. 
2. In this example, we could use GetCommission() instead (with the right parameters). You should use an advanced SQL statement only when needed. This is just an example.

Normalizing SQL Query Results

A SQL query will only return one value (not an array of values).

After executing a SQL statement, always convert using ToString(), ToDecimal(), ToDate().

If there is no match, we will get NULL which is neither a string, decimal or date.

For example, if we have this:

var $id = {{SELECT TOP 1 Id FROM <something> WHERE <something>}}

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

var $id = ToString($id)

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

IIF($id = '', <this>, <that>)

If we do not call ToString(), and the result in NULL, we may receive an error saying that NULL cannot be compared to a string.

For example, if we have this:

var $rev = {{SELECT TOP 1 Revenue FROM <something> WHERE <something>}}

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

var $rev = ToDecimal($rev)

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

IIF($rev <> 0, <this>, <that>)

If we do not call ToDecimal(), and the result in NULL, we may receive an error saying that NULL cannot be compared to a number.

Pre-Loading Data

Advanced SQL statements are individual queries to a remote database with billions of records. 

Those remote database queries, if frequent, can be expensive. 

For example, suppose that we want to cache some information about transactions in the current calculation period.

We want to cache the Transaction Date, the Rep name, and the Revenue.

We only want to cache transactions which are in the current calculation period.

First, we must add an advanced option with SqlCacheQuery=<CacheTableName>={{Advanced SQL Statement}}. 

This advanced option allows us to pre-load data.

Make sure to use aliases (AS) to name retrieved fields.

      SqlCacheQuery=MyCache={{SELECT Id, [Rep (Imported)] AS Rep, [Revenue (System)] AS Rev FROM Transactions WHERE WorkspaceId = @WorkspaceId AND IsDeleted = 0 AND TransactionDate >= @CalculationStartDateUtc AND TransactionDate < @CalculationEndDateUtc}}

Only fields Id, Rep, and Rev will be loaded.

This allows us to limit the amount of data loaded.

Second, our advanced SQL statement can now query cached data (instead of the remote database).

We can use this formula to determine if there is a duplicate transaction with the same Rep and Revenue:

      var $id = [Transaction].[id]

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

      var $rep = [Transaction].[Rep (Imported)]

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

      var $rev = [Transaction].[Revenue (System)]

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

      var $otherId = {{SELECT Id FROM MyCache WHERE Id <> $id AND Rep = $rep AND Rev = $rev}}

Note: you do not need to specify WHERE WorkspaceId = @WorkspaceId when querying cached data as the data is already scoped to the current workspace.

The query will run much faster because we're querying a local database instead of a remote one.

The local database only has a small sub-set of the whole database and will be loaded in memory. 

This diagram describes the difference between querying the remote database vs. pre-loading the data and querying a local database. 

Optimizing Advanced SQL Queries

The following principles can be used to optimize advanced SQL queries.

When possible:

1. Pre-load data where appropriate
   
1. Please refer to paragraph "Pre-Loading Data" above
2. For Transactions, reference Native or System fields.
   
1. Imported fields are not indexed.
   
3. Move less expensive checks ahead of SQL queries.
   
1. The order in which you evaluate transactions matters.
2. For example, suppose that you require transactions to have a payment date in period AND not to be a duplicate of prior transactions (this requires a SQL query).
   
3. It's more performant to first check the payment date - if not in period, you can exit "early" and avoid a SQL query. 
   
4. It's only when the payment date is in period that you will evaluate the SQL query.
4. Specify a date condition when possible.
1. If only records prior to a specific date need to be queried, specify a date condition to limit the number of candidate records.
   
2. For example, if you want to check if there is a duplicate older transaction, only query transactions dated before the current one.
   
3. Generally, you also only want to query commissions and credits associated with calculations before the current one.   
   
5. Check if a record exists instead of getting a count.
   
1. Checking for the TOP 1 record is faster than getting an exact count of records.
   
2. See "Recipe - Checking If A Record Exists" below.
6. Only join the entities you need.
   
1. Each join has a performance cost as requires a match.
7. Exploit caching (advanced).
1. The result of SQL queries are cached.
   
2. Formulating the same SQL query 1000 times will not result in 1000 database calls.
   
3. There are cases where it is possible to exploit this characteristics to optimize SQL queries.
8. Use WITH (NOLOCK) after each table name (advanced).
1. A query such as "SELECT TOP <something> FROM Transactions AS T WITH (NOLOCK)" allows the database engine to bypass locked records. 
   
2. This is useful when the database is under heavy contention.
   
9. Use advanced options to join transactions with transactions (advanced)
   
1. If you need to match transactions with other transactions, consider using advanced options.
   
2. See "Joining Transactions With Other Transactions" below.

Recipe - Checking If A Record Exists

The expensive and naive approach is to count matching records.

var $count = {{SELECT SUM(*) FROM <something> WHERE <something>}}

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

var $count = ToDecimal($count)

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

IIF($count >= 1, <this>, <that>)

However, do we really need a count? 

Asking SQL to get a count requires matching all records.

It is cheaper to ask SQL to find the first matching record like this.

Here, SQL will read the ID and stop if not empty.

We will get the ID of the first record.

var $id = {{SELECT TOP 1 Id FROM <something> WHERE <something>}}

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

var $id = ToString($id)

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

IIF($id <> '', <this>, <that>)

There is a lazier option which is to use the SQL trick "SELECT TOP 1 1".

If there is a match, we will SELECT 1. Otherwise, we will SELECT NULL. 

Note that we call ToDecimal() to convert NULL to zero.

var $exists= {{SELECT TOP 1 1 FROM <something> WHERE <something>}}

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

var $exists = ToDecimal($exist)

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

IIF($exists = 1, <this>, <that>)

Recipe - Check If Transaction Is The First One For A Given Customer

Formula:

var $customer = [Transaction].[Customer (System)]

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

var $date = [Transaction].[Transaction Date (System)]

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

var $firstId = {{SELECT TOP 1 Id FROM Transactions WHERE [Customer (System)] = $customer AND WorkspaceId = @WorkspaceId AND IsDeleted = 0 AND TransactionDate <= $date ORDER BY TransactionDate ASC, Id ASC}}

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

var $firstId = ToString($firstId)

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

var $id = [Transaction].[id]

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

var $firstTransaction = IIF($id = $firstId, 1, 0)

1. We read the Customer of the current transaction
   
2. We get the Id of the oldest transaction for the same Customer in the history of transactions
1. We sort by transaction date ascending (oldest first)
   
1. If two transactions have the exact same transaction date, we sort by Id ascending to ensure a predictable order (second-level order)
   
2. We match on the Customer
   
3. We exclude any transaction whose date is after the current date (optimization)
4. We exclude any transaction not in the current workspace
   
5. We exclude any recently deleted transaction
   
3. We compare our current transaction ID with the fist transaction ID for this customer
   
1. If equal, our current transaction is the first for this customer
   
2. If different, our current transaction is NOT the first for this customer 

var $customer = [Transaction].[Customer (System)]

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

var $dealId = [Transaction].[Deal ID (Imported)]

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

var $id = [Transaction].[id]

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

var $rep = {{SELECT TOP 1 [Rep (Imported)] FROM Transactions WHERE [Customer (System)] = $customer AND [Deal ID (Imported)] = $dealId AND [Rep (Imported)] IS NOT NULL AND Id <> $id AND WorkspaceId = @WorkspaceId AND IsDeleted = 0}}

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

var $rep = ToString($rep)

1. We read the Customer of the current transaction
   
2. We read the Deal ID of the current transaction
3. We get the Rep from a matching transaction in the history of transactions
1. We match on the Customer
   
2. We also match on the Deal ID
   
3. We only select transactions whose Rep is not empty
   
4. We ensure we do not match ourselves
   
5. We exclude any transaction not in the current workspace
   
6. We exclude any recently deleted transaction