Search for a contact with a given saint value
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 statusesAmount_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.