Nil activity query - no activity in last 10 days

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Nil activity query - no activity in last 10 days

Author

Topic

BridgeCat
Starting Member

1 Post

Posted - 2012-06-22 : 21:05:54

I have a database set up as part of my customer relationship management system.

Customers have many call notes attached to them.

What I am trying to do is develop a query to pull Distinct accounts with no call notes attached with a date in the last 10 days.

The current query I am using is as follows

select distinct account.name, callnotes.date_held
from account, callnotes
where and callnotes.status = 'Held'
and callnotes.date_held NOT BETWEEN DATE_ADD(CURDATE(), INTERVAL -10 DAY) AND CURDATE() ORDER BY `name` ASC

However this just brings a list of all call notes attached to the account not in the last 10 days regardless of whether they have actually had a call in the last 10 days.

Basically my staff will be using this list as a action of the most important clients to call - ie clients who have been ignore for over 10 days.

I Hope that makes sense. The only way i can think of it working is to run a query to find the call note with the most recent date held, and if it is greater than 10days then display the record.

Can any body help with this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-06-22 : 21:52:22

Looks like you are not using MS SQL Server. SQLTeam is on MS SQL Server. For other try asking at dbforums.com
Anyway the query below is for MS SQL Server.


select account.name, max(callnotes.date_held)
from   account
       inner join callnotes on account.account_no = callnotes.account_no
where callnotes.status = 'Held'
and   callnotes.date_held < DATEADD(DAY, -10, GETDATE())
group by account.name
ORDER BY account.name ASC

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources