Hi,
I am creating a new Advanced Report to track changes of certain field on opportunity - Signature Date, Probability, New ARR.
From the documentation, I was able to start with this:
SELECT IFNULL(opportunities.id,'') opportunity_id
,IFNULL(opportunities.name,'') accounts_name
,IFNULL(opportunities_audit.date_created,'') `date_created`
,IFNULL(opportunities_audit.created_by,'') `created_by`
,IFNULL(opportunities_audit.field_name,'') `field_name`
,IFNULL(opportunities_audit.before_value_string,'') `before`
,IFNULL(opportunities_audit.after_value_string,'') `after`
FROM opportunities
LEFT JOIN opportunities_audit opportunities_audit ON opportunities.id = opportunities_audit.parent_id
WHERE (((opportunities_audit.date_created >= DATE_SUB(CURDATE(), INTERVAL 14 DAY)))) AND opportunities.deleted=0;
I wanted to add my filters and conditions to that query:
SELECT IFNULL(opportunities.id,'') opportunity_id
,IFNULL(opportunities.name,'') accounts_name
,IFNULL(opportunities_audit.date_created,'') `date_created`
,IFNULL(opportunities_audit.created_by,'') `created_by`
,IFNULL(opportunities_audit.field_name,'') `field_name`
,IFNULL(opportunities_audit.before_value_string,'') `before`
,IFNULL(opportunities_audit.after_value_string,'') `after`
FROM opportunities
LEFT JOIN opportunities_audit opportunities_audit ON opportunities.id = opportunities_audit.parent_id
WHERE (((opportunities_audit.date_created >= DATE_SUB(CURDATE(), INTERVAL 14 DAY)))) AND opportunities.deleted=0;
AND opportunities.probability BETWEEN 5 AND 95
AND opportunities.opportunity_type = 'New Business'
AND opportunities_audit.field_name IN ('date_closed', 'probability_c', ‘calculated_new_arr_c');
Unfortunately this query is not working. Is there a mistake in the SQL? Or is the SQL limited and I cannot add more conditions?
Thank you for your help!
Ada
