Hello Ada Napierala, 

Can you clarify where are you running this query? 

When trying it to run against a MySQL Database the error that I have is related to the opportunities.date_created field that doesn't exist. 
Replacing it with opportunities.date_entered the query runs fine.

Hi Andre Serrazina,

Yes I just realized that I was using incorrect field name. However, I also realized that the field date_closed is showing me the signature date of the opportunity, which is not the date I want to use. 

I want to use the date from activity stream - the date a user changed the opportunity to closed won. 

Is that possible? 

Andre Serrazina

For my advanced report, I am also looking for the following information (apologies, I am a rookie on Advanced Reports in Sugar): 

1. What is the SQL engine of SugarCRM? Is it MySQL, SQL Server, etc? Need to understand this in order to know what syntax I should use for functions. So far I've inferred that it is MySQL, but I just wanted to be sure.

2. Can I perform a subquery inside a query? For example, I would like to do SELECT * FROM (SELECT * FROM opportunities), but it is not letting me. It's outputting a "Page Not Found" error when I try to save the query.

Point 2 is very important for me to compute models for calculations. Thanks!

SELECT * FROM (SELECT * FROM opportunities) as op

 you should be able to do something like this, it is not working for you 'cos you forgot to add the table name. 

that should work 

Hi, Ada Napierala, on Sugar Cloud Sugar runs MySQL. 

I wonder what the goal of this report is? Maybe we can help more efficient when knowing what you want to archive

Bests

Björn

You are right, the  date_closed stores the Expected Close date that can be set by the user before the Opportunity is closed.
The information that you are looking for is not stored by default in Sugar. If you want to store it at an Opportunity level you'll need to create a field and populate it when the Opportunity is closed. This can be done via SugarBPM, Logic Hook or workflow.   

Retrieving that info from the activity stream will not be easy, the data column in the activities table will store the record name, id and the before and after value of the field that is audited. 

You can have a look at what is stored in the activities table for a given Opportunity querying for that row like this: 

SELECT * FROM activities where parent_id={opportunity_id};    

Andre Serrazina

I think the close date will be sufficient for now. 

Thank you for all your help!

Best, 

Ada

Thank you Rodrigo Manara

Andre Serrazina thanks for confirming :)