SQL Advanced Reports

Hello

I have been trying to create a custom SQL query using Advanced Reports.
I am using the opportunities_audit table which has all changes to an opportunity as events.

I would like to create a range of date validity for a given event, by applying a window function in the code.
This means that for each row (which is an event) I would like to compute lead(date_created) over (partition by parent_id order by date_created).

I could then compute a table with a date dimension (I.e. one column called 'date', with date fields, '2021-01-01', '2021-01-02') and then join that to
the other table on the basis that the date is between the date_created and lead(date_created).

This would give me for a given date in the past, the state of all opportunities in a given point in time.

However, when I try to save the query with this window function, I get a Not Found page.

Could someone help please?
Thanks

0 André Lopes over 2 years ago

Hi Ada

Can you share the sql query you are trying to execute?

André Lopes
Lampada Global

Skype: andre.lampada
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Ada Napierala over 2 years ago in reply to André Lopes

Hi André Lopes

This is the query: select parent_id, event_id, date_created, lead(date_created) over (partition by parent_id order by date_created) from opportunities_audit where parent_id = 'x'

I am unsure if the MySQL engine supports window functions, so maybe this can't work?

Is there any way to leverage subqueries? I.e select * from (select * from opportunities) ?

Thanks,
Ada
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Björn Canales Pfisterer over 2 years ago in reply to Ada Napierala

Hi Ada Napierala,

this might help you https://support.sugarcrm.com/Documentation/Sugar_Versions/11.3/Sell/Application_Guide/Reports/Advanced_Reports/#Interlocking_ParentChild_Queries

Bests

Björn
Cancel
Vote Up +1 Vote Down

Sign in to reply

Verify Answer

Cancel
0 André Lopes over 2 years ago in reply to Ada Napierala

Definitely Ada Napierala, the error you faced is caused by MySQL server no being able to recognize some functions. I just remembered we faced this same error message on trying to execute a subquery.

André Lopes
Lampada Global

Skype: andre.lampada
Cancel
Vote Up +1 Vote Down

Sign in to reply

Verify Answer

Cancel

0 Björn Canales Pfisterer over 2 years ago in reply to André Lopes

mhm, take this as a base select that is working and surprinsingly orders the events in the correct order ascending by time Ö_Ü

select opa.id, opa.parent_id, opa.event_id, opa.date_created, opa.created_by, opa.date_updated, opa.field_name, opa.data_type, opa.before_value_string, opa.after_value_string, opa.before_value_text, opa.after_value_text from opportunities_audit as opa
 JOIN opportunities as ops on (opa.parent_id = ops.id) order by opa.parent_id, opa.date_created;

Bests

Björn