I want to create an advanced report to track opportunities that have not had any updates to the Next Steps field in the past 30 days, but am having trouble with the query. Can anyone give suggestions on how to write this query?
Hi Patrick Comune
Pre-condition: such field must be audited.
Having said that you can either:
Thanks for the help. I tweaked my query, but still is coming up invalid. Sorry, I'm new to SQL... Could you take a look and let me know where I am making errors? (Query below...)
SELECT IFNULL(opportunities.id,'') opportunity_id,IFNULL(opportunities.name,'') next_step,IFNULL(opportunities_audit.date_created,'') `date_created`,IFNULL(opportunities_audit.created_by,'') `created_by`
FROM opportunitiesINNER JOIN opportunities_audit opportunities_audit ON next_step WHERE (((opportunities_audit.date_created <= DATE_SUB(CURDATE(), INTERVAL 30 DAY))))AND opportunities.deleted=0;
The query may be something like that:
SELECT opportunities.id opportunity_id, opportunities.name opportunity_name, opportunities_audit.date_created date_created, opportunities_audit.created_by created_byFROM opportunitiesINNER JOIN opportunities_audit opportunities_audit ON opportunities.id = opportunities_audit.parent_id AND opportunities_audit.field_name = 'next_step'WHERE opportunities_audit.date_created <= DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND opportunities.deleted = 0;
Retrieving data ...