Unique Accounts

You could try following the out of the box report for "Leads with No Calls, Meetings, Tasks, or Emails" and seeing if you can follow that same logic for Accounts but you will need to go a couple more layers deeper than the Lead report does because the Call/Email could be related to a Contact related to the Account rather than directly to the Account.

And because you don't just want those with NO Calls or Emails but rather no calls or emails within a certain date range it gets much more complicated, and potentially impossible to sort out without an Advanced Custom Query Report

https://support.sugarcrm.com/Documentation/Sugar_Versions/12.3/Sell/Application_Guide/Reports/Advanced_Reports/index.html#Custom_Query_Fields

FrancescaS

Hi Francesca, thanks for this!

We followed this and we were able to get some progress. I have a SQL code for the custom query we built, are you able to assist? We tried this but certain Call Types are still being counted in the report (exclude Text Message and Outbound Attempt).

SELECT
accounts.id AS account_id,
accounts_cstm.practice_ref_c AS Practice_Ref,
accounts.name AS Account_Name,
accounts_cstm.success_manager_c AS Success_Manager,
accounts_cstm.status_c AS Status,
accounts_cstm.potential_c AS Potential,
COUNT(DISTINCT calls.id) AS call_count,
COUNT(DISTINCT emails.id) AS email_count
FROM accounts
LEFT JOIN calls ON accounts.id = calls.parent_id AND calls.parent_type = 'Accounts'
AND calls.deleted = 0 AND calls.date_start BETWEEN '2023-03-01' AND '2023-03-31'
LEFT JOIN calls_cstm ON calls.id = calls_cstm.id_c AND calls_cstm.call_type_c NOT IN ('Call Attempt', 'Text Message')
LEFT JOIN emails ON accounts.id = emails.parent_id AND emails.parent_type = 'Accounts'
AND emails.deleted = 0 AND emails.date_sent BETWEEN '2023-03-01' AND '2023-03-31'
LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c
WHERE accounts.account_type = 'Customer' AND accounts_cstm.status_c != 'Deactivated' AND accounts.deleted = 0
GROUP BY accounts.id
ORDER BY Account_Name;

Check the dropdown for your status_c in Studio, it is possible that 'Call Attempt' and 'Text Message' are the labels, not the database values.
The query will want the database values in the filter, but the report is likely translating those into labels before it displays.

FrancescaS

If you want to count emails sent last month, regardless of when the report is run you can use:

-- /* Greater or equal to the start of last month */
AND DATE(emails.date_sent) >= DATE(DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH))), INTERVAL 1 DAY)
-- /* Smaller or equal than one month ago */
AND DATE(emails.date_sent) <= DATE(DATE_SUB(NOW(), INTERVAL 1 MONTH))

Remember that Database Dates are UTC, so there may be some discrepancies depending on your timezone that you may want to take care of.

If you want to count emails sent last month, regardless of when the report is run you can use:

-- /* Greater or equal to the start of last month */
AND DATE(emails.date_sent) >= DATE(DATE_ADD(LAST_DAY(DATE_SUB(NOW(), INTERVAL 2 MONTH))), INTERVAL 1 DAY)
-- /* Smaller or equal than one month ago */
AND DATE(emails.date_sent) <= DATE(DATE_SUB(NOW(), INTERVAL 1 MONTH))

Remember that Database Dates are UTC, so there may be some discrepancies depending on your timezone that you may want to take care of.