AfricanHeart
Reputation: 21
SQL find Customers that haven't been contacted in the past 45 days
Could you please help me filter by customers who haven't been contacted in the past xxxx days? e.i 45
SELECT TOP 1000 [ID]
,[dates]
,[companies]
FROM [company].[dbo].[Contact]
NB: some companies have been contacted more than once on xxxx day.
I assume getting the latest date will get the accurate result.
ID dates companies
2017/01/02 Facebook
2017/01/03 Chevron
2017/01/05 AFLAC
2017/01/09 Applied Industrial Technologies
2017/01/11 Charter Communications
2017/01/12 Coca-Cola
2017/01/13 Dow Chemical
2017/01/17 Foot Locker
2017/01/18 General Dynamics
2017/01/20 Humana
2017/01/25 Imation
2017/01/26 Kimberly-Clark
2017/01/27 3M
2017/01/30 Facebook
2017/01/31 Chevron
2017/02/01 Foot Locker
2017/02/03 General Dynamics
2017/02/06 Humana
2017/02/10 Imation
2017/02/17 Kimberly-Clark
2017/02/20 3M
2017/02/21 Public Storage
2017/02/22 Qualcomm
2017/02/24 Sealy
2017/02/28 Steelcase
2017/02/02 Textron
2017/02/06 Men's Wearhouse
2017/02/08 Toll Brothers
2017/02/09 Tractor Supply
2017/02/13 USG
2017/02/14 Valspar
2017/02/15 Waste Management
2017/02/20 Williams-Sonoma
2017/02/21 Yahoo
2017/02/23 Union Pacific
2017/02/24 Foot Locker
2017/02/27 General Dynamics
2017/02/28 Humana
2017/03/01 Imation
2017/03/02 Kimberly-Clark
2017/03/03 3M
2017/03/06 Public Storage
2017/03/09 Qualcomm
2017/03/10 Sealy
2017/03/13 Steelcase
2017/03/14 Textron
2017/03/17 Men's Wearhouse
2017/03/23 Toll Brothers
2017/03/24 Tractor Supply
2017/03/27 USG
2017/03/01 Valspar
2017/03/03 Waste Management
2017/03/06 Williams-Sonoma
2017/03/07 Yahoo
2017/03/08 Union Pacific
2017/03/09 Foot Locker
2017/03/13 General Dynamics
2017/03/15 Humana
2017/03/22 Imation
2017/03/24 Kimberly-Clark
2017/03/27 3M
2017/03/28 Capital One Financial
2017/03/29 CBS
2017/04/03 Citigroup
2017/04/05 E*Trade Financial
2017/04/06 El Paso
2017/04/07 Goodrich
2017/04/10 Hanesbrands
2017/04/11 International Paper
2017/04/12 Eastman Kodak
2017/04/20 Kraft Foods
2017/04/25 LSI
2017/04/26 Macy's
2017/04/27 Brunswick
2017/04/28 Meredith
2017/04/03 Manpower
2017/04/04 NCR
2017/04/05 Oracle
2017/04/07 Pepsi Bottling
2017/04/10 Pfizer
2017/04/13 Penn National Gaming
2017/04/14 Prudential Financial
2017/04/17 RadioShack
2017/04/18 Regal Entertainment Group
2017/04/24 SanDisk
2017/04/26 Facebook
2017/05/01 Chevron
2017/05/04 AFLAC
2017/05/05 Applied Industrial Technologies
2017/05/08 Charter Communications
2017/05/09 Coca-Cola
2017/05/10 Dow Chemical
2017/05/11 Foot Locker
2017/05/15 General Dynamics
2017/05/16 Humana
2017/05/17 Imation
2017/05/19 Kimberly-Clark
2017/05/24 3M
2017/05/25 Facebook
2017/05/26 Chevron
2017/05/01 Foot Locker
2017/05/04 General Dynamics
2017/05/08 Humana
2017/05/12 Imation
2017/05/15 Kimberly-Clark
2017/05/16 3M
2017/05/18 Public Storage
2017/05/19 Qualcomm
2017/05/22 Sealy
2017/05/24 Steelcase
2017/05/26 Textron
2017/05/29 Men's Wearhouse
2017/05/30 Toll Brothers
2017/05/31 Tractor Supply
2017/06/07 USG
2017/06/09 Facebook
2017/06/13 Chevron
2017/06/14 AFLAC
2017/06/15 Applied Industrial Technologies
2017/06/19 Charter Communications
2017/06/20 Coca-Cola
2017/06/22 Dow Chemical
2017/06/23 Foot Locker
2017/06/26 General Dynamics
2017/06/28 Humana
2017/06/01 Imation
2017/06/02 Kimberly-Clark
2017/06/05 3M
2017/06/06 Facebook
2017/06/07 Chevron
2017/06/08 Foot Locker
2017/06/12 General Dynamics
2017/06/13 Humana
2017/06/14 Imation
2017/06/16 Kimberly-Clark
2017/06/20 3M
2017/06/21 Public Storage
2017/06/22 Qualcomm
2017/06/29 Sealy
2017/06/30 Steelcase
2017/07/11 Textron
2017/07/12 Men's Wearhouse
2017/07/13 Toll Brothers
2017/07/14 Tractor Supply
2017/07/19 USG
2017/07/21 Facebook
2017/07/24 Chevron
2017/07/25 AFLAC
2017/07/26 Applied Industrial Technologies
2017/07/27 Charter Communications
2017/08/01 Coca-Cola
2017/08/02 Dow Chemical
2017/08/10 Foot Locker
2017/08/11 General Dynamics
2017/08/15 Humana
2017/08/16 Imation
2017/08/17 Kimberly-Clark
2017/08/21 3M
2017/08/23 Facebook
2017/08/24 Chevron
2017/08/25 Foot Locker
2017/08/29 General Dynamics
2017/08/30 Humana
2017/08/31 Imation
2017/09/07 Kimberly-Clark
2017/09/08 3M
2017/09/13 Public Storage
2017/09/14 Qualcomm
2017/09/15 Sealy
2017/09/20 Steelcase
2017/09/21 Textron
2017/09/22 Men's Wearhouse
2017/09/25 Toll Brothers
2017/09/26 Tractor Supply
2017/09/27 USG
2017/09/04 Facebook
2017/09/05 Chevron
2017/09/06 AFLAC
2017/09/11 Applied Industrial Technologies
2017/09/13 Charter Communications
2017/09/14 Coca-Cola
2017/09/15 Dow Chemical
2017/09/20 Foot Locker
2017/09/21 General Dynamics
2017/09/22 Humana
2017/09/26 Imation
2017/09/27 Kimberly-Clark
2017/09/29 3M
2017/10/02 Facebook
2017/10/03 Chevron
2017/10/05 Foot Locker
2017/10/11 General Dynamics
2017/10/12 Humana
2017/10/16 Imation
2017/10/18 Kimberly-Clark
2017/10/19 3M
2017/10/20 Public Storage
2017/10/23 Qualcomm
2017/10/24 Sealy
2017/10/26 Steelcase
Upvotes: 1
Views: 224
Answers (3)
SqlZim
Reputation: 38053
using not exists():
select distinct companies
from [company].[dbo].[Contact] c
where not exists (
select 1
from [company].[dbo].[Contact] i
where i.companies = c.companies
and i.dates >= convert(date,dateadd(day,-45,getdate()))
)
If you have a table of companies then you can use that instead of distinct on your contact table. This will also enable you to return companies that have no entry in contact.
select *
from [company].[dbo].[companies] c
where not exists (
select 1
from [company].[dbo].[Contact] i
where i.companies = c.companies
and i.dates >= convert(date,dateadd(day,-45,getdate()))
)
Another option using aggregation with the having() clause to only return those companies where the max(dates) is less than 45 days ago; also returning the latest contact date:
select
companies
, LastContact = max(dates)
from [company].[dbo].[Contact] c
group by companies
having max(dates)<convert(date,dateadd(day,-45,getdate()))
rextester demo: http://rextester.com/OJER22556
returns:
+---------------------------+-------------+
| companies | LastContact |
+---------------------------+-------------+
| Brunswick | 2017-04-27 |
| Capital One Financial | 2017-03-28 |
| CBS | 2017-03-29 |
| Citigroup | 2017-04-03 |
| E*Trade Financial | 2017-04-05 |
| Eastman Kodak | 2017-04-12 |
| El Paso | 2017-04-06 |
| Goodrich | 2017-04-07 |
| Hanesbrands | 2017-04-10 |
| International Paper | 2017-04-11 |
| Kraft Foods | 2017-04-20 |
| LSI | 2017-04-25 |
| Macy's | 2017-04-26 |
| Manpower | 2017-04-03 |
| Meredith | 2017-04-28 |
| NCR | 2017-04-04 |
| Oracle | 2017-04-05 |
| Penn National Gaming | 2017-04-13 |
| Pepsi Bottling | 2017-04-07 |
| Pfizer | 2017-04-10 |
| Prudential Financial | 2017-04-14 |
| RadioShack | 2017-04-17 |
| Regal Entertainment Group | 2017-04-18 |
| SanDisk | 2017-04-24 |
| Union Pacific | 2017-03-08 |
| Valspar | 2017-03-01 |
| Waste Management | 2017-03-03 |
| Williams-Sonoma | 2017-03-06 |
| Yahoo | 2017-03-07 |
+---------------------------+-------------+
Upvotes: 2
user7715598
Reputation:
SELECT Companies
,[Dates]
,DATEDIFF(Day, [Dates], CAST(Getdate() AS DATE)) AS [DaysHaven't Been Contacted Past45 days]
FROM #Temp o
WHERE DATEDIFF(Day, [Dates], CAST(Getdate() AS DATE)) >= 45
ORDER BY [Dates] DESC
Upvotes: 0
pim
Reputation: 12587
declare @date datetime = getdate()
select * from [company].[dbo].[contact] where DATEDIFF(DAY, dates, @date) >= 45
Since performing an action on the filter predicate is bad for performance (preventing index use etc. etc.) a more performant approach would be:
declare @date datetime = getdate()
select
id
,dates
,company
from (
select
*,
DATEDIFF(DAY, dates, @date) as daysSinceContact
from
[company].[dbo].[contact]
) as c
where
daysSince >= 45
OR using a CTE
declare @date datetime = getdate()
;with c as (
select
*,
DATEDIFF(DAY, dates, @date) as daysSinceContact
from
[company].[dbo].[contact]
)
select
id
,dates
,company
from
c
where
daysSince >= 45
Upvotes: 0
Related Questions
- Display customers who haven't made a purchase in less than 30 days
- How to get customers who have most purchases in past 365 days, but none before?
- Show all customers who havent placed an order in a specific time frame
- MSSQL 30-60-90-120 day query grouping
- Query to get the customers who haven't transacted in the past two months
- How do I get Contacts that have not been contacted in the last X days?
- How to find if user not logged in for 45 days
- SQL Query For Customers Not Used For Last 3 Years
- T-SQL Finding Accounts With Contact Dates within 30 days
- SQL Find accounts with no collection activity in the last 45 days