Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-13
8
Medium Priority
?
383 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1000 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 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

916 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