Solved

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

Posted on 2008-10-13
8
377 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

809 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