How Do Advanced SQL Statements Work?

How Do Advanced SQL Statements Work?

What Are Advanced SQL Statements?
Our technical team may configure advanced SQL statements which operate directly against the database. This is done a/ for performance reasons, b/ to perform complex queries spanning multiple entities, or c/ to analyze your entire commission or transaction history. Those advanced SQL statements are always contained within {{...}} markers. As a customer, you cannot edit those statements - only our technical team may do so.

When Are Advanced SQL Statements Necessary?
In most cases, functions such as QueryTransactionSingle(), QueryTransactionSum(), GetCommission(), GetCommisssionRate(), etc. - are sufficient to match records, check the commission history, etc.

However, even though flexible, those functions cannot satisfy all scenarios.
For example, they may not allow you to:
  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
For example, while a function such as QueryTransactionSingle() is powerful, it may not be sufficient because:
  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

    Which Entities Can Be Accessed By Advanced SQL Statements?
    You can access all entities and their associated native SQL fields:
    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
    Please note that you will need to use a plural name.
    For example, if the entity is called CalculationCommission, the table will be called CalculationCommissions.  


    How Are Advanced SQL Statements Protected?
    Only Sales Cookie administrators can add or modify advanced SQL statements.
    If a customer alters a SQL statement, a security alert will be generated.
    Sales Cookie uses a checksum to prevent alteration of advanced SQL statements.
    Also, specific keywords are banned (ex: DROP, DELETE, etc.).

    Advanced SQL statements MUST include a security clause such as "WHERE WorkspaceId = @WorkspaceId AND IsDeleted = 0".
    Otherwise, your query could access entities from other workspaces, which is not acceptable.
    Also, your query could access recently deleted records, which is not acceptable.
    Remember, your query is converted into a native SQL database query, so security is paramount.
    We require you to specify this security clause manually to ensure you know what you are doing.
    In some cases, you will need to prefix WorkspaceId and IsDeleted with an entity alias (please see below for examples). 


    Which Entities Can I Query For?
    Most of the time, you will query one of the following:
    1. CalculationCommissions
    2. CalculationCredits
    3. Transactions
     It's uncommon to query for other entities such as:
    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):
     

    CalculationCommission Queries
    Each CalculationCommission record stores one per-transaction commission.
    Each CalculationCommission record is linked to a Calculation (itself linked to a Plan). 
    Each CalculationCommission record is linked to a CalculationCredit (itself linked to a Transaction). 

    The sample query below shows joins from CalculationCommission records to:
    1. Related Calculation > Plan
    2. Related CalculationCredit > Transaction
    Recommendations:
    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
    Sample query:
    SELECT SUM(CC.Value) FROM CalculationCommissions AS CC // get the sum of commission amounts
    JOIN Calculations AS C ON CC.CalculationId = C.Id // join from calculation commission to associated calculation
    JOIN Plans AS P ON C.PlanId = P.Id // join from associated calculation to associated plan
    JOIN CalculationCredits AS CR ON CC.CalculationCreditId = CR.Id // join from calculation commission to associated transaction credit 
    JOIN Transactions AS T ON CR.TransactionId = T.Id // join from associated calculation credit to associated transaction
    WHERE CC.WorkspaceId = @WorkspaceId AND CC.IsDeleted = 0 AND C.IsDeleted = 0 // restrict to current workspace + ignore deleted data
    AND C.EndDate <= $endDate // exclude calculation commissions from future calculations
    AND CC.IsEstimated = 0 AND CR.BeneficiaryId = $userId AND C.PlanId = $planId // exclude estimated commissions, specific payee, specific plan
    AND CC.Explanation LIKE '%Clawback%' // only calculation commissions labeled as clawbacks
    AND CR.TransactionId = $tid // for a specific transaction only

    Note:
    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.

    CalculationCredit Queries
    Each CalculationCredit record stores one individual per-transaction credit.
    Each CalculationCredit record is linked to a Calculation (itself linked to a Plan). 
    Each CalculationCredit record is linked to a Transaction.

    The sample query below shows joins from CalculationCredit records to:
    1. Related Calculation > Plan
    2. Related Transaction
    Recommendations:
    1. For performance, remove joins you do not need
    2. Ignore credits from calculations later than the current calculation (otherwise future calculations could affect past ones)
    3. Exclude records outside of the current workspace
    4. Exclude recently deleted records
    Sample query:
    SELECT TOP 1 CR.Value FROM CalculationCredits AS CR // get first credited amount
    JOIN Calculations AS C ON CR.CalculationId = C.Id //join from calculation credit to associated calculation
    JOIN Plans AS P ON C.PlanId = P.Id // join from associated calculation to associated plan
    JOIN Transactions AS T ON CR.TransactionId = T.Id // join from transaction credit to associated transaction
    WHERE CR.WorkspaceId = @WorkspaceId AND CR.IsDeleted = 0 AND C.IsDeleted = 0 // restrict to current workspace + ignore deleted data
    AND C.EndDate <= $endDate // exclude calculation credits from future calculations
    AND CR.TargetSystemUserId = $userId AND C.PlanId = $planId // specific credited user, specific plan
    AND CR.TransactionId = $tid // for a specific transaction only
    ORDER BY CR.TransactionDate DESC // most recent calculation credit by transaction date

    Note:
    1. We have CR.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 (CalculationCredit) and the related Calculation. 

    Transaction Queries
    Each Transaction is linked to a TransactionBatch, which represents the associated data source.

    For transactions only, you can refer to either:
    1. Native SQL fields such as Transaction.Revenue, Transaction.CustomerName, etc.
    2. System fields such as [Transaction].[Revenue (System)], Transaction].[Customer (System)], etc.
    3. Imported fields such as [Transaction].[Amount (Imported)], Transaction].[Account (Imported)], etc. 
    Using options 1 or 2 is more performant than option 3.
    You can use a combination of approaches in the same advanced SQL query.

    The sample query below shows joins from Transaction records to:
    1. Related TransactionBatch
    Recommendations:
    1. For performance, reference Native or System fields when possible
    2. For performance, remove joins you do not need
    3. Exclude records outside of the current workspace
    4. Exclude recently deleted records
    Sample query:
    SELECT TOP 1 T.Revenue FROM Transactions AS T // referencing native SQL field on Transaction entity (fast)
    JOIN TransactionBatches AS TB ON T.TransactionBatchId = TB.Id // join from transaction to associated transaction batch
    WHERE T.WorkspaceId = @WorkspaceId AND T.IsDeleted = 0 // restrict to current workspace + ignore deleted data
    AND T.[Owner / Sold By (System)] LIKE '%Fred%' // referencing system field on Transaction entity (fast)
    AND T.[Field #1 (Imported)] LIKE '%hardware%' // imported field on Transaction entity (slow)
    AND TB.DataSource LIKE '%Stripe%' // Stripe transactions only
    AND TB.Query LIKE '%Invoice%'// invoices only
    ORDER BY T.TransactionDate DESC // most recent by transaction date

    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.


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

    When possible:
    1. For Transactions, reference Native or System fields.
      1. Imported fields are not indexed.
    2. 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.
    3. 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.   
    4. 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.
    5. Only join the entities you need.
      1. Each join has a performance cost as requires a match.
    6. 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.
    7. 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.
    8. 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)

    Explanations:
    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 

    Recipe - Match On Multiple Fields And Return Single Value
    Formula:
    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)
    Explanations:
    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

    Joining Transactions With Other Transactions
    Suppose that you have 2 or more data sources. Typically, you process each transaction individually, one at a time. You may perform a SQL query for each transaction to match it to another transaction in another data source. This can be expensive if you have many transactions to process. For example, if you have 10K transactions, and you must match each one individually to another transaction, 10K SQL queries are required.

    Advanced options can be used to join transactions with other transactions at data retrieval time. This allows you to join transactions with other transactions at data retrieval time - instead of individually, one at a time. Your join can be based on 1 or more fields. Please refer to "Join Options" in advanced option documentation.

      • Related Articles

      • Which Advanced Options Are Available?

        Sales Cookie offers many advanced capabilities via its web interface - including draws, splits, overrides, etc. However, some additional (even more) advanced options can also be set on incentive plans using a text-based representation. Advanced ...

      • Error - Your Plan Contains Advanced Formulas Which Must Be Approved By Us

        Applicability This error applies to the following situation: A plan was configured by our technical team You altered formulas within the plan Some of those formulas contain {{...}} expressions Your calculation is failing with this error Impact This ...

      • How Does Plan Version Tracking Work?

        Plan Snapshot When you run a calculation, we take a full snapshot of your plan. This means you can always go back to any calculation and review how the plan was setup at the time your calculation ran. To view a calculation's plan snapshot: Edit your ...

      • How Does The Payroll Gateway Work?

        You can always retrieve commission-related data using the OData API (programmatically - or using BI tools such as Excel, Tableau, Microsoft Power BI). This lets you retrieve transactions, rewards, users, teams, plans, calculations, etc. However, ...

      • How Do Email Notifications Work When Releasing Credits / Rewards?

        When you release credits or rewards, you have the option to notify payees via email. Only payees with a non-zero estimated OR actual calculated reward (for the released calculation) will receive an email. Payees which have no payouts will not be ...