We wanted to be able to create a report that will calculate opportunity aging without creating a bunch of new fields in Sugar. The idea behind this is I can create an Advanced Report query that would add calculated values for the "time in stage". I came up with the following script but the advanced report UI won't save it and it would say invalid query. Can I get help in figuring out what I need to correct here?
SELECT
IFNULL(opportunities.id, '') primaryid,
IFNULL(opportunities.name, '') opportunities_name,
opportunities.date_entered Date_Entered,
opportunities_cstm.territory_c Territory,
IFNULL(opportunities.opportunity_type, '') Type,
IFNULL(opportunities.lead_source, '') Lead_Source,
opportunities.probability Probability,
opportunities.amount Estimated_Value,
IFNULL(opportunities.sales_stage, '') Sales_Stage,
opportunities.date_closed Expected_Close_Date,
opportunities_cstm.actual_close_date_c Actual_Close_Date,
IFNULL(l1.id, '') Account_id,
IFNULL(l1.name, '') Account_Name,
l1_cstm.customer_class_c Customer_Class,
GETDATE() RunDate, //This is to capture when the report was ran
l1_cstm.customer_class_description_c Customer_Class_Desc,
opportunities_cstm.total_age_in_days_c Age_In_Days,
opportunities_cstm.timestamp_status_champion_c Champion_Date,
DATEDIFF(DD,opportunities_cstm.timestamp_status_champion_c,GETDATE()) as Champion_Age, //This is to formula to calculate days between champion status time stamp and RunDate
opportunities_cstm.timestamp_status_closed_c Close_Date,
opportunities_cstm.timestamp_status_discovery_c Discovery_Date,
opportunities_cstm.timestamp_status_evaluation_c Evaluation_Date,
opportunities_cstm.timestamp_status_negotiation_c Negotiation_Date,
opportunities_cstm.timestamp_status_qualify_c Qualification_Date,
opportunities_cstm.timestamp_status_stakeholder_c Stakeholders_Date,
opportunities_cstm.timestamp_status_won_c Closed_Won_Date
FROM
opportunities
LEFT JOIN
accounts_opportunities l1_1
ON opportunities.id = l1_1.opportunity_id
AND l1_1.deleted = 0
LEFT JOIN
accounts l1
ON l1.id = l1_1.account_id
AND l1.deleted = 0
LEFT JOIN
opportunities_cstm opportunities_cstm
ON opportunities.id = opportunities_cstm.id_c
LEFT JOIN
accounts_cstm l1_cstm
ON l1.id = l1_cstm.id_c
WHERE
(
((opportunities.date_closed > '2020-01-01' )
AND
(
opportunities.date_entered < '2020-05-04 07:00:00'
)
)
)
AND opportunities.deleted = 0
