Hello!
I am designing one query where I have to convert the value from one currency to another. The problem is for I have to calculate a value with following conditions: In that transaction row, if the current row currency (A) is not equal to the posting currency (B) then I will have to go to currency conversion table to get the conversion rate with condition From_currency = A to To_currency = B and Valid to and from dates. And after getting that conversion rate, I will have to convert the value equivalent in currency B (value*conversion rate/100). I am not able to design the query which has all these ifs and calculations. Can someone help??
Regards
PP
April 14, 2021 at 3:07 pm
You will also need the date (at least), since currency conversion values change over time.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 14, 2021 at 3:19 pm
Yes. I forgot to mention, I have the valid from and to date.
April 14, 2021 at 3:25 pm
Something like:
CASE WHEN A.currency = B.currency THEN A.value ELSE A.value * C.conversion_rate/100 END
Or, to simplify logic, if you have a self reference in the conversion table for every currency (A => A) you could eliminate the case statement and just always return the converted value.
I'm assuming/hoping, that since you showed dividing conversion rate by 100, that is actually true in all the conversions.
SELECT
m.A AS original_currency, m.B AS posting_currency,
/*...,*/
m.value AS original_value,
COALESCE(m.value * cc.conversion_rate / 100.0, m.value) AS posting_value
FROM dbo.main m
LEFT OUTER JOIN dbo.currency_conversion cc ON
m.A <> m.B AND
cc.from_currency = m.A AND
cc.to_currency = m.B AND
cc.conversion_date = m.Date
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 23, 2021 at 9:22 pm
Please do some research and look up the E you rules for triangulation. This means that you have to convert from currency A to Euros, then from Euros to currency B. There are some other rules and regulations about doing these conversions that you shall have to research.
Please post DDL and follow ANSI/ISO standards when asking for help.
April 26, 2021 at 8:46 pm
Please do some research and look up the E you rules for triangulation. This means that you have to convert from currency A to Euros, then from Euros to currency B. There are some other rules and regulations about doing these conversions that you shall have to research.
"E you"? That's rather bizarre! It's "EU", right!?
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 26, 2021 at 9:51 pm
Yes EU. I dictate most of my correspondence these days because my hands are such a mess. First I couldn't live without a spelling checker, now I sing praises of the Dragon Dictate. The horrors of old age!
Please post DDL and follow ANSI/ISO standards when asking for help.
April 28, 2021 at 4:10 pm
Ooh, yeah, that is rough. Sorry, it must be a royal pain (no pun intended).
When I was a kid, my grandmother used to say, "Never get old." As I got a bit older, I'd say, "but grandma, the alternative is not so good!"
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
May 18, 2021 at 8:00 am
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply