Coming soon: Your brand new Help Center & Community! Get a sneak-peek here

SQL query with SALE.ammount and SUM(SALE.ammount) in differrent currencies

Hello,

We are currently connecting to the SO database with a query.

We are selecting sales from a certain date and showing (amongst others SALE.ammount). This ammount seems to be of a certain currency.

 

How can we create a sum of all the SALE.ammounts if they are in different currencies?

RE: SQL query with SALE.ammount and SUM(SALE.ammount) in differrent currencies

the currency table contains a field "rate", which can be used to convert any currency to euro.
The following query will translate ay sale to euro:

select s.sale_id, s.heading, s.amount, s.amount/c.rate as euro,s.currency_id, c.name, c.rate, c.units, c.isocode,s.*
from crm7.sale s join crm7.CURRENCY c on c.Currency_id=s.currency_id

The currency rates are maintained in the admin-client, but it is not really very welll suited to the task, since you would have to update it manually every day in order to have precise answers.  A better solution is to run a daily routine to automatically update the rates from a webservice.

 

Af: Inge Simon Thorbjørnsen 6. nov 2017

RE: SQL query with SALE.ammount and SUM(SALE.ammount) in differrent currencies

Just in case you are using the Win client and havn't heard about the show totals capabilities in the archives.
When currency is enabled, it will even let you choose what currency to display the sum in. All properly live-updated with in-memory data :-)

Conrad

 

 

Af: Conrad Weyns 6. nov 2017

RE: SQL query with SALE.ammount and SUM(SALE.ammount) in differrent currencies

And still not really accurate, as to convert between currencies you'd have to register the exchange rate on the date the sale was invoiced. If you pay me 100 euros today and I deposit it in NOK it doesn't mean it will still be worth 100 euros next week

Af: Jason Tiler Broad 21. feb 2018

RE: SQL query with SALE.ammount and SUM(SALE.ammount) in differrent currencies

Well, that's why God made ERP systems.

SuperOffice is more focused on potential sales and their potential value, than being an a system of record for cross-border currency transactions. ERP systems don't like potential values that have dates in the future.

If this is a big issue for your customers, then make a wish.

Af: Christian Mogensen 21. feb 2018