How to query an opportunity field not updated in 30 days

Hi Patrick Comune 

Pre-condition: such field must be audited.

Having said that you can either:

• create an INNER JOIN to opportunities_audit, filtering by parent_id (opportunity_id), field_name (the field to look for last update) and date_created (the date when that field has been updated);
• create a subquery on opportunities_audit, filtering by parent_id (opportunity_id), field_name (the field to look for last update) and date_created (the date when that field has been updated);

Regards

Hi Andre,

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...)

Thanks,

Patrick

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 opportunities
INNER 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_by
FROM opportunities
INNER 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;

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_by
FROM opportunities
INNER 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;