Solved

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

Posted on 2008-10-13
8
374 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
  • 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
 
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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 142

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Help - 12 42
GRANT, REVOKE, DENY 4 20
T-SQL Using IN with a subquery 3 12
Log Backup 2 13
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now