Solved

SQL Help - Select all with no occurances in the last 30 days

Posted on 2008-10-13
8
380 Views
Last Modified: 2012-06-27
Hi All, We have an SQL Databse that manages our CRM, a table in the database (tblCRMActivity) has a record in it for every time we have been in contact with a customer.

We want to be able to pull out every customer in the database that we have not had contact with in the last 30 days. Now I have been able to pull out all records that HAVE occurred in the last 30 days (code Below), but I need all customers that have had no contact with us in the last 3 days, basically the opposite.

Is this possible in SQL alone, or am I back to filtering records with some nested loops in VB.NET?
SELECT   tblCompany.strCompanyName, tblCompany.ID
FROM         tblCompany INNER JOIN
                      tblCRMActivity ON tblCompany.ID = tblCRMActivity.intCompanyID INNER JOIN
                      tblDomainAccountMap ON tblCRMActivity.intEngineerID = tblDomainAccountMap.intUserID
Where dtActionDate < DATEADD(day, -30, getdate())

Open in new window

0
Comment
Question by:mgordon-spi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 17

Expert Comment

by:HuyBD
ID: 22700987
Please explain more detail
>>but I need all customers that have had no contact with us in the last 3 days

SELECT   tblCompany.strCompanyName, tblCompany.ID
FROM         tblCompany INNER JOIN
                      tblCRMActivity ON tblCompany.ID = tblCRMActivity.intCompanyID INNER JOIN
                      tblDomainAccountMap ON tblCRMActivity.intEngineerID = tblDomainAccountMap.intUserID
Where dtActionDate < DATEADD(day, -3, getdate())

Open in new window

0
 
LVL 8

Expert Comment

by:Jupiler78
ID: 22700993
Hello mgordon-spi,

SELECT   tblCompany.strCompanyName, tblCompany.ID
FROM         tblCompany LEFT OUTER JOIN
                      tblCRMActivity ON tblCompany.ID = tblCRMActivity.intCompanyID INNER JOIN
                      tblDomainAccountMap ON tblCRMActivity.intEngineerID = tblDomainAccountMap.intUserID
Where dtActionDate < DATEADD(day, -30, getdate()) and tblCRMActivity.intCompanyID is Null

Regards,

Jupiler78
0
 

Author Comment

by:mgordon-spi
ID: 22701006
Sorry, I'm not sure why I would be looking for intcompanyID being Null? Every Record in tblCRMActivity will have a company ID.

I need to return a list of companies that do not have records in tblCRMActivity that have been added in the last 30 days.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Expert Comment

by:Jupiler78
ID: 22701030
You are right, I'm sorry.

I meant the unique ID of tblCRMActivity, as when you do a left join, all companies are being processed but all those who don't have an activityrecord will be shown, that is a part of what you need.

The other part, of the companies with activities but not in the last 30 days, that I'll still have to test for myself. And for the syntax, I don't have SQL server here right now.

Jupiler78
0
 
LVL 22

Expert Comment

by:dportas
ID: 22701033
I seriously suggest you stop using Hungarian notation for column names. It's a bad idea for lots of reasons and I don't know of anyone who recommends it.

SELECT strCompanyName, ID
FROM tblCompany
WHERE NOT EXISTS
(SELECT 1
 FROM tblCRMActivity A
 JOIN tblDomainAccountMap M
  ON A.intEngineerID = M.intUserID
 WHERE dtActionDate BETWEEN DATEADD(DAY, -30, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
  AND C.ID = A.intCompanyID);
0
 
LVL 22

Accepted Solution

by:
dportas earned 250 total points
ID: 22701039
Correction:

SELECT strCompanyName, ID
FROM tblCompany C
WHERE NOT EXISTS
(SELECT 1
 FROM tblCRMActivity A
 JOIN tblDomainAccountMap M
  ON A.intEngineerID = M.intUserID
 WHERE dtActionDate BETWEEN DATEADD(DAY, -30, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
  AND C.ID = A.intCompanyID);
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 22701043
you are actually close:
SELECT   c.strCompanyName, c.ID
FROM      tblCompany c
LEFT JOIN  tblCRMActivity  a
  ON c.ID = a.intCompanyID 
 AND a.dtActionDate >= DATEADD(day, -30, getdate())
WHERE a.intCompanyID  IS NULL

Open in new window

0
 

Author Comment

by:mgordon-spi
ID: 22701108
Thanks People! That actualy makes perfect sense. I seem to be getting the same output from both so I will award points in two directions. Point aken about the hungarian notation - didn't reliase I was so far off the mark, any suggestions on a better naming convention?
0

Featured Post

Stressed Out?

Watch some penguins on the livecam!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question