CODEWITHSUNDEEP
sqlsql-server
papapi
papapi

Reputation: 61

How do I get Contacts that have not been contacted in the last X days?

Although I have used not exists and other ways, still I'm battling to come up with a solid way to get a list on contacts that have not been contacted in the last XXX days,

Here is my query and a snapshot of the result

SELECT   top 100   

Todo_tbl.todo_ID,
Contact_tbl.Contact_ID,  ---e.i 34
Contact_tbl.Contact_Name, ---e.i John papa
Todo_tbl.Title, ---e.i Sent him an email
Todo_tbl.StartDate ---e.i 2018-10-10 16:50:18.017

FROM            

Todo_tbl INNER JOIN
Contact_tbl ON Todo_tbl.Contact_ID = Contact_tbl.Contact_ID


WHERE Todo_tbl.StartDate>60 <-- **wrong**
order by Todo_tbl.StartDate desc

todo_ID Contact_ID  Contact_Name                Title       StartDate
646555  14755       Fox Browne                  Phone       2018-06-06 16:50:18.017
646553  31791       Weickl                      Email       2018-06-06 16:47:03.000
646551  26735       Nexp                        Email       2018-06-06 16:42:19.000
646550  33264       Stooo                       Email       2018-06-06 16:40:54.000
646548  14769       Heyuu                       Email       2018-06-06 16:39:19.000
646547  14738       Dakota                      Email       2018-06-06 16:37:49.000
646560  36720       IHelo                       Email       2018-06-06 16:00:00.000

Upvotes: 1

Views: 81

Answers (2)

apomene
apomene

Reputation: 14389

I believe you are looking for:

MySQL

   ...
        WHERE Todo_tbl.StartDate <now() - interval 7 day

SQL Server

..
WHERE Todo_tbl.StartDate <GETDATE()-7

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Try this with left join and use condition Todo_tbl.Contact_ID is null

SELECT   top 100   

Todo_tbl.todo_ID,
Contact_tbl.Contact_ID,  
Contact_tbl.Contact_Name, -
Todo_tbl.Title, 
Todo_tbl.StartDate 

FROM   Contact_tbl left join         
Todo_tbl on
Todo_tbl.Contact_ID = Contact_tbl.Contact_ID
WHERE Todo_tbl.StartDate< cast(GETDATE()-7 as date) and Todo_tbl.Contact_ID is null
order by Todo_tbl.StartDate

Upvotes: 1

Related Questions