Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-13
8
Medium Priority
?
382 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

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…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

662 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