Question:
I am trying to write a Query in SalesLogix that returns a list of contacts that have no history recorded in the last xx days. How can I do this?
Answer:
Here is an example of a Query for no history in the last 30 days.
A – In the Sales Client, go to the contact list view.
B – Right-click and select “Add Group”.
C – On the property tab, name the group.
D – Select the conditions tab.
E – Highlight the contact table in the upper left side pane.
F – Double-click the contactId field in the upper right hand pane.
G – In the operator drop down, select “IN”.
H – In the value window, copy and paste the following SQL statement:
Select distinct ContactId from History where DateDiff(day, History.Createdate,GetDate()) < 30
alt:
–No contact in Last 90 Days
Select C.ContactID
From Contact C
Where not Exists
(Select Distinct H.ContactID
From history H
Where H.CompletedDate > DateAdd(m, -3, getutcdate()) and
H.ContactID = C.ContactID and
H.Type <> 262156)
I – Select OK.
J – Highlight the condition, right click and select “NOT”.
K – Select OK.
