T-SQL code help

Question

T-SQL code help

piu.scorpio

Old Hand

Points: 353
More actions
April 14, 2021 at 2:55 pm
Go to Answer
#3871067
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
- This topic was modified 3 years, 7 months ago by piu.scorpio.
ScottPletcher

SSC Guru

Points: 101098
More actions
April 14, 2021 at 3:42 pm
Answer
#3871110
```
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

You will also need the date (at least), since currency conversion values change over time.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

piu.scorpio Old Hand Points: 353 More actions · Answer 2

Yes. I forgot to mention, I have the valid from and to date.

ratbak Ten Centuries Points: 1358 More actions · Answer 3

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.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 4

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.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 5

jcelko212 32090 wrote:

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 6

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.

ScottPletcher SSC Guru Points: 101098 More actions · Answer 7

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".