Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Copper Super Contributor
Posts: 42
Registered: ‎04-23-2009

No history in last xx days

I'm having difficulty getting the parameters correct for a query that returns a list of contacts that have not had a history recorded in the last xx days.  Perhaps this is better accomplished via reports but am looking for someone to jog my memory as I'm pretty sure we've done this in the past.

 

Thanks,

 

Dennis

Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: No history in last xx days

Here's how to build a (LAN/Windows) group to do this:

 

This example is for NO history in the last 30 days. You just need to replace 30 (in step H) in the Select statement with whatever number you want (ex: 90)
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.

 

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
New Member
Posts: 2
Registered: ‎03-02-2011

Re: No history in last xx days

Hi,

 

I am having a similar issue and tried the following statement in LAN query builder as you had suggested and got no results.

Is there anything I am doing wrong?

 

Help.

 

Sheeba

 

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
I - Select OK.
J - Highlight the condition, right click and select "NOT".
K - Select OK.
Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: No history in last xx days

Did you check the checkbox: Use Value as Literal?

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
New Member
Posts: 2
Registered: ‎03-02-2011

Re: No history in last xx days

Yes, tried that still no luck. This is the exact statement I pasted and then checked the box "value is literal".

 

Select distinct Contactid from History where DateDiff(day, History.Createdate,GetDate()) < 120

 

Thanks for your help.

Highlighted
New Member
Posts: 15
Registered: ‎12-07-2010

Re: No history in last xx days

put brackets around your statement