AnsweredAssumed Answered

The Contacts without calls problem

Question asked by Mayer Elyashiv on Jun 7, 2015
Latest reply on Jun 15, 2015 by elelolo
Hi,
i'm on a SugarCRM 6.5.20 CE istance.
I need to find all Contacts or Leads who haven't been called in last 3 months.

I tried to solve it thanks to KReporter but it allows me just to find Contacts or Leads who haven't been called at all.

So i tried with Dispage Enhanced Search and i wrote two queries for that:

Contacts

SELECTcontacts.id ,
LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name, ''), ' ',
IFNULL(contacts.last_name, '')))) as name,
contacts.first_name ,
contacts.last_name ,
contacts.salutation ,
accounts.name account_name,
jtl0.account_id account_id,
contacts.title ,
contacts.primary_address_city ,
contacts.primary_address_state ,
contacts.phone_work ,
jt1.user_name assigned_user_name ,
jt1.created_by assigned_user_name_owner ,
'Users' assigned_user_name_mod,
contacts.date_entered ,
contacts.assigned_user_id
FROM
contacts
LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c
LEFT JOIN accounts_contacts jtl0 ON contacts.id=jtl0.contact_id
AND jtl0.deleted=0
LEFT JOIN accounts accounts ON accounts.id=jtl0.account_id
AND accounts.deleted=0
AND accounts.deleted=0
LEFT JOIN users jt1 ON contacts.assigned_user_id=jt1.id
AND jt1.deleted=0
AND jt1.deleted=0
where
contacts.deleted=0
AND (contacts.id NOT IN (
SELECT
contacts.id
FROM
contacts
RIGHT JOIN calls_contacts ON
contacts.id=calls_contacts.contact_id
)
OR contacts.id NOT IN(
SELECT
contacts.id
FROM
contacts
INNER JOIN calls_contacts ON
contacts.id=calls_contacts.contact_id
INNER JOIN calls ON calls.id=calls_contacts.call_id
WHERE
calls.date_start >= '2015-05-05 22:00:00'
AND calls.date_start <= '2015-09-05 21:59:59'
) )
ORDER BY
account_name ASC

Leads
it's almost the same, but since i have 500 Contacts and 5000 Leads, the query is so slow that it doesn't work.

Could you give me any advice to improve the query performance?

Outcomes