Follow Me On...
« Setting created and modified user via SugarCRM Webservice API | Main | Creating an "API Only" User in SugarCRM »
Monday
Jul292013

SugarCRM, SQL query to determine Sales Cycle From New Account --> Customer

SQL query below runs against SugarCRM Audit tables to determine how long it took an Account record to become a “Customer”.  It looks at the audit tables to calculate when the field was changed.

 

SELECT accounts.name, accounts.industry, accounts_audit.before_value_string, accounts_audit.after_value_string, 
accounts.date_entered,accounts_audit.date_created AS “Became Customer”, TIMESTAMPDIFF(DAY, accounts.date_entered, accounts_audit.date_created) AS “How Long”
FROM accounts_audit
JOIN accounts ON accounts_audit.parent_id = accounts.id
JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c
WHERE after_value_string = “Customer” AND accounts.deleted = “0”

SELECT accounts.name, accounts.industry, accounts_audit.before_value_string, accounts_audit.after_value_string, accounts.date_entered,accounts_audit.date_created AS “Became Customer”, TIMESTAMPDIFF(DAY, accounts.date_entered, accounts_audit.date_created) AS “How Long”FROM accounts_auditJOIN accounts ON accounts_audit.parent_id = accounts.idJOIN accounts_cstm ON accounts.id = accounts_cstm.id_cWHERE after_value_string = “Customer” AND accounts.deleted = “0”

 

 

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
All HTML will be escaped. Hyperlinks will be created for URLs automatically.