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? 

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!

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};