SQL query error

Hello, 

I am trying to create this query to calculate opportunity age:

SELECT opportunities.id opportunity_id, opportunities.name opportunity_name, datediff(date(now()), date(opportunities.date_created)) opportunity_age_in_days, date(opportunities.date_created) created_at, date(opportunities.date_modified) updated_at, date(opportunities.date_closed) as closed_at
FROM opportunities

however I keep getting this error: 

Not Found

The requested URL was not found on this server.

Could you please let me know why?

Parents Reply Children
  • Hey

    I followed your steps to recreate the Opp Age. I do get the opp Age, but it's incorrect :( 

    Also, your closing date seems to be an imprint, not something you add yourself, whereas mine is empty and to be added manually on all opps, including Closed Won. 

  • Hey, yes I played with test data, and set the closing date with a mass update...

    for updaing existing records do the following:

    Go to the field settings of the new closing date field and set the follwing calculated formula (I chosed expected closedate as you mentioned above it will work for you)

    Tthen go to the opportunities Module list view, mark all opportunities

    Then run Recalculate values

    Once you are done, remove the formula you have set above.
    Now all existing Opps have expected close date = closing date.
    If the formula you have set on Opp Age is correct it should also be calculated fine.

    For all new Opps SugarBMP should set the date automatically

    Bests

    Björn

  • Hey

    Yes, the process is working, however it has a flaw unfortunately. Opportunities that are with Signature Date e.g. 30.06.2023 and created in 30.06.2022, but are already closed lost will still show the opp age of 365 days... 

    Do you have any solution for that? Is there no way of capturing the Date closed of the opportunity as the date it has been physically closed lost / won by the sales person? 

    Thanks 

    Ada

  • So you mean because the workflow only sets the date to "today" when the Opp is won? Create a second SugarBPM that does the same if a Opp get lost.

    Or is this for existing Opps that are already lost and you are looking for the correct timestamp? If this is the case I think this might be solved with an SQL statement via database similiar to the one you've asked for initally but in my experience if we implement a new solution in an existing instance especially "lost opps" from the past are not that important and can be filtered out for a while in reports if everyone else using this reports agrees.

    If an SQL statement is needed for updating the records keep in mind Sugar Support is able to run it on your instance but you need to deliver the working SQL statement to them.

    Hope that helps :-)

    Bests

    Björn