Search for a contact with a given saint value

•

Version: 10

• 2 minutes to read

The following example shows how we can retrieve a contact by using a SAINT value. Each counterValue row points to the contact_id or project_id it is linked to.

Note

To use SAINT, a separate license should be obtained. It also requires that the administrator has defined one or more SAINT statuses and generated SAINT status values on the database.

Some of the counter values for a contact:

SELECT * FROM countervalue WHERE sale_status = 2 AND amountClassId = 0 AND totalReg > 2

If we want to find all contacts with no sales registered in the particular period, we could write the SQL statements as follows.

SELECT * FROM countervalue WHERE contact_id > 0 AND sale_Status = 4 AND amountClassId = 0 AND totalRegInPeriod = 0

Sale_Status= 4 includes all statuses
Amount_Class = 0 includes all sizes

If we want to find contacts who have more than 5 sales register the following code can be used.

SELECT * FROM CounterValue WHERE contact_id > 0 AND sale_Status = 4 AND amountClassId = 0 AND totalReg > 5

If we want to find contacts with more than 4 follow-up calls (record_type = 5 ) registered in this period.

SELECT * FROM CounterValue WHERE contact_id > 0 AND record_type = 5 AND direction > 0 AND intent_id = 0 AND totalReg > 4

Note

We are required to specify the intent_id for follow-ups/documents to avoid duplicates IDs in the result. intent_id = 0 includes all intents.

If we want to find all contacts with more than 4 follow-up call (record_type=5 on task) registered in this period:

SELECT * FROM CounterValue WHERE contact_id > 0 AND record_type = 5 AND direction > 0 AND intent_id = 0 AND totalReg > 4

We must specify intent_id for follow-ups/documents to avoid duplicate IDs in the result. intent_id = 0 implies all intents.