Details about this thread
  • State Not Answered
  • Replies 5 replies
  • Subscribers 86 subscribers
  • Views 3018 views
  • Users 0 members are here

SQL Advanced Reports

Ada Napierala

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

Parents
  • 0

    Hi Ada

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

    André Lopes
    Lampada Global
    Skype: andre.lampada
  • 0 in reply to André Lopes

    Hi

    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

Reply
  • 0 in reply to André Lopes

    Hi

    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

Children