Searches on SAINT values
Each counterValue row points to the contact_id or project_id it is linked to. The counter values themselves are stored in the totalReg, totalRegInPeriod, notCompleted, notCompletedInPeriod, and so on fields.
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.
Here are some of the counter values for a project:
SELECT * FROM countervalue where project_id =47 and sale_status in (1,2,4) and amountclassid in (2,1,0)
| sale_status | amountClassId | totalReg | totalRegInPeriod | notCompleted | lastRegistered | ... |
|---|---|---|---|---|---|---|
| 1 | 1 | 8 | 0 | 8 | 2021-11-05 | |
| 1 | 2 | 1 | 1 | 1 | 2021-11-05 | |
| 1 | 0 | 11 | 1 | 11 | 2021-11-05 | |
| 2 | 1 | 6 | 0 | 6 | 2021-11-05 | |
| 2 | 2 | 0 | 0 | 0 | 2021-11-05 | |
| 2 | 0 | 9 | 0 | 9 | 2021-11-05 | |
| 4 | 1 | 14 | 0 | 14 | 2021-11-05 | |
| 4 | 2 | 1 | 1 | 1 | 2021-11-05 | |
| 5 | 0 | 21 | 1 | 21 | 2021-11-05 |
If we want to search on the SAINT counters, we would use the counter-value fields as criteria and read out the project_id or contact_id.
If we wanted to find all projects where there is an open sale, in any size, and no sale has been registered in the past year:
SELECT project_id FROM CounterValue WHERE project_id > 0 AND sale_Status = 1 AND amountClassId=0 AND lastRegistered < '2005.10.1'
If we only wanted to search for small sales, we would use the amount-class "small" (amountclass_id=1)
SELECT project_id FROM CounterValue WHERE project_id > 0 AND sale_Status = 1 AND amountClassId=1 AND lastRegistered < '2005.10.1'
If we want to find all contacts with no sales registered in the period, we would search like this:
SELECT contact_id, project_id FROM CounterValue WHERE contact_id > 0 AND sale_Status = 4 AND amountClassId=0 AND totalRegInPeriod =0
- Sale-status = 4 (All sales)
- amount-class = 0 (all sizes)
If we want to find all contacts with more than 5 sales registered (since the beginning of time):
SELECT contact_id, project_id FROM CounterValue WHERE contact_id > 0 AND sale_Status = 4 AND amountClassId = 0 AND totalReg > 5
If we want to find all contacts with more than 4 follow-up calls (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
Note
We must specify intent_id for follow-up/documents to avoid duplicate IDs in the result. intent = 0 implies all intents.