No history in last xx days

[dennisscott]

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

[RJLedger]

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.

[smike]

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.

[RJLedger]

Did you check the checkbox: Use Value as Literal?

[smike]

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.

[leongort]

put brackets around your statement