October 21, 2008 at 1:55 am
Hi,
I am trying to build a report through a view which gives me Amount in dollars but now i need to convert that in AED, i have a seperate table which captures the exchange rates, now how can i convert the amount from $ to AED in query itself, does anybody have any sample query by which i can understand the process.
TIA.
October 21, 2008 at 3:07 am
Given your amount-bearing table is "position" and the column name holding the amount in $ is "salesamount",
and given your currency table is "currency" having a column "shortcut" holding the value "AED" and a column "exchangerate" holding the amount of AED for 1 $.
Your statement would be
Select
p.somecolumns
, round(p.salesamount*c.exchangerate, @NumberOfDigitsAfterComma)
, p.morecolumns
from position p
inner join currency c
on c.shortcut='AED'
if your exchangerate is given in $ per 1 AED, you'd write
Select
p.somecolumns
, round(p.salesamount/c.exchangerate, @NumberOfDigitsAfterComma)
, p.morecolumns
from position p
inner join currency c
on c.shortcut='AED'
devloping robust and performant databaseapplications with Microsoft SQL-Server
October 22, 2008 at 8:13 am
While the previous post will handle a single amount converted at a fixed rate, I have to wonder what you need to do with historical information? There are two ways to go - each individual contributing amount can be converted "as of" the date it represents, provided that the exchange rate table has the exchange rates for all possible dates in the range of data that you have. Alternatively, you can convert all historical amounts at the current exchange rate, but I'm not sure that would provide a "useful" report. If you need to report on historical data, provide more detail on exactly what's needed.
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
October 26, 2008 at 5:04 am
I have a column called type, by which i can take the required exchange rate, i am getting my desired result, i need to cross check the data for verification.
thanks to all, for providing me the solution.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply