Details about this thread
  • State Not Answered
  • Replies 3 replies
  • Subscribers 294 subscribers
  • Views 740 views
  • Users 0 members are here
Related content from around SugarClub

How to query an opportunity field not updated in 30 days

Patrick Comune

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?

Thanks,

Patrick

Parents
  • 0

    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

    André Lopes
    Lampada Global
    Skype: andre.lampada
Reply
  • 0

    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

    André Lopes
    Lampada Global
    Skype: andre.lampada
Children
  • 0 in reply to André Lopes

    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;