CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2005
Malik
Malik

Reputation: 207

How to Handle Multi Currencies (Precision and Scale) from one Column in SQL Query

I have a three columns in table

TransactionDate---DateTime
TotalAmount---DECIMAL(18,4)
CurrencyCode---nVarchar(10)

TotalAmount-------------CurrencyCode
1000.5000----------------KD (Kuwaiti Dinnar) which must be 1000.500
5500.2000----------------ETB (Eithopian Birr)
2500.1500----------------USD (United States Dollars) which must be 2500.15

I am storing these multi currencies in one column......now i want to get report for each day or month by using SUM() Function in SQL Query.............but SUM must be done based on each currency's precision or scale. i.e.....If currency is USD then 

SUM(2500.15)

If currency is KD then 

SUM(1000.500)

and If ETB then 

SUM(5500.2000)

some thing like this..

Upvotes: 1

Views: 1692

Answers (4)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

SELECT CurrencyCode, SUM(TotalAmount)
FROM dbo.your_table
GROUP BY CurrencyCode

Upvotes: 0

Asif
Asif

Reputation: 2677

This cannot be achieved by using one column, you should used three columns for each currency sum and set their precision accordingly e.g.

    Select SUM(2500.15) as USD, SUM(1000.500) as KD, SUM(5500.2000) as ETB

Upvotes: 0

Brad
Brad

Reputation: 635

Based off your question (vague) you are trying to convert currencies to your type of currency?

i think this is what you might be what your example data looks like:

TransactionDate TotalAmount CurrencyCode
2012-06-01      12          KD
2012-06-01      25          KD
2012-06-01      55          ETB
2012-06-01       6          ETB
2012-06-01      13          USD
2012-06-01      10          USD

And what logic you are trying to accomplish is ex. If CurrencyCode = KD then sum(TotalAmount)*conversion rate (assuming that is what the numbers that you listed were)

Query:

Select
  sum(
    case when [CurrencyCode] = 'KD' then [TotalAmount]*1000.500 
        when [CurrencyCode] = 'ETB' then [TotalAmount]*5500.2000 
        when [CurrencyCode] = 'USD' then [TotalAmount]*2500.1500 
        else 0 end) as [TotalCalcAmount]
From [SomeTable]
--Where [TransactionDate] between '2012-06-01' and '2012-06-30' --Commented out for test purposes

SQLFiddle to go with explanation: http://sqlfiddle.com/#!3/ade6e/3

Upvotes: 0

nairbv
nairbv

Reputation: 4323

You should probably just store each currency in its smallest possible unit, as an integer.

For example, if you're storing USD, you should store the value as a "number of pennies" instead of a decimal number of dollars with precision 2. $1.00 becomes 100.

When you do your "SUM," you'll probably need to "group by currency" for the results to make any sense. Your client software will need to know how to properly display each currency, but don't let that interfere with how the data is stored in your database.

Upvotes: 1

Related Questions