1

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
Filburt
  • 17,626
  • 12
  • 64
  • 115
AfricanHeart
  • 21
  • 1
  • 8

3 Answers3

2

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  |
+---------------------------+-------------+
SqlZim
  • 37,248
  • 6
  • 41
  • 59
0
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
pim
  • 12,019
  • 6
  • 66
  • 69
  • Hi, thank you very much for your reply, however, I get an error in SQL Server Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '='. Msg 137, Level 15, State 2, Line 10 Must declare the scalar variable "@date". – AfricanHeart Jun 27 '17 at 11:35
  • @AfricanHeart So sorry, stupid me, I forgot the variable type (no coffee yet)! Answer updated. – pim Jun 27 '17 at 11:37
  • there is no difference perfomance wise in the 2 approaches; a CTE performs as a query. https://stackoverflow.com/questions/706972/difference-between-cte-and-subquery – Paolo Jun 27 '17 at 11:39
  • @paolo I meant between the first query and the second. Not the derived table vs the CTE. Though if we're being exacting here. There are several instances where a derived table will out perform a CTE. – pim Jun 27 '17 at 11:43
  • @AfricanHeart my pleasure! Glad it works. Would love an upvote and correct marker if you wouldn't mind :) – pim Jun 27 '17 at 11:44
  • 2
    Performance may get better if you tranfer the calculation on the right side like dates>=DATEADD(day, DATEDIFF(day, 0, GETDATE()), -45) – niktrs Jun 27 '17 at 11:53
  • @PimBrouwers all 3 solution apply a formula on the data and filter on the result so any index cannot be used; no index is present on the computed columns actually... – Paolo Jun 27 '17 at 11:55
  • @Paolo Your Solution Works 100%! Many thanks! however I notice that DATEDIFF(DAY only filters data to only show from -45 days ago. the thought behind this is to find the "clients who hasn't been contacted in the last 45 days" MANY THANKS – AfricanHeart Jun 27 '17 at 11:59
0
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