May 18, 2016 at 10:57 pm
Hi,
I have a table of invoices with different currency types, AUD, USD, EUR, INR, RUP, CND etc... the final solution requires all of them converted to AUD, EUR and USD.
Set 1 AUD
I can convert them using total*rate e.g 13600*1.39 combined with a where currency = 'USD' or *1.00 where currency = 'SGD'. This combine with UNION function will achieve what i need but it's not very efficient or elegant. So there is a select for each currency type with it conversion rate, but can anyone suggest a better way?
Table
Country, divisionbuyer,Invoice_id, TotalIncTax, Currency, date
Australia,acme,1211, 1200, USD, 2016-12-06
Australia,david inc, 333, 3300, SGD, 2016-08-25
Australia, franks LTD, 5000, AUD,2016-07-22
Australia, Lions, 1000000, INR, 2016-08-09
select
Country
,company_id
,DivisionBuyer
,[Month]
,SUM([total invoices])as [Total Invoices]
,'$' + REPLACE(CONVERT(varchar(20), (CAST(SUM([Total Inc GST]) AS money)), 1), '.00', '') as [Total Inc GST]
,Currency
from
(
select
Country
,h.DivisionBuyer
, h.company_id
,datename(Month,date) as [Month]
,count(h.invoiceid) as [Total Invoices]
, CASE WHEN Currency = 'SGD' THEN (l.TotalInclGST * 1.00) END AS [Total Inc GST]
, CASE WHEN Currency = 'SGD' THEN replace(currency, 'SGD','AUD') END AS [Currency]
from [dbo].[AUS_Invoice_Header] h
inner join [dbo].[Companies] c
on h.company_id = c.company_id
inner join [dbo].[AUS_Invoice_Line] l
on h.InvoiceID = l.InvoiceID
Where Currency = 'SGD'
group by h.company_id,h.DivisionBuyer,country,datename(Month,date),currency
UNION
select
Country
,h.DivisionBuyer
, h.company_id
,datename(Month,date) as [Month]
,count(h.invoiceid) as [Total Invoices]
,CASE WHEN Currency = 'USD' THEN (l.TotalInclGST * 1.37) END AS [Total Inc GST]
, CASE WHEN Currency = 'USD' THEN replace(currency, 'USD','AUD') END AS [Currency]
,currency
from [dbo].[AUS_Invoice_Header] h
inner join [dbo].[Companies] c
on h.company_id = c.company_id
inner join [dbo].[AUS_Invoice_Line] l
on h.InvoiceID = l.InvoiceID
Where Currency = 'USD'
group by h.company_id,h.DivisionBuyer,country,datename(Month,date),currency
UNION
select
Country
,h.DivisionBuyer
, h.company_id
,datename(Month,date) as [Month]
,count(h.invoiceid) as [Total Invoices]
,CAST(SUM(l.TotalInclGST) AS money) as [Total Inc GST]
,Currency
from [dbo].[AUS_Invoice_Header] h
inner join [dbo].[Companies] c
on h.company_id = c.company_id
inner join [dbo].[AUS_Invoice_Line] l
on h.InvoiceID = l.InvoiceID
where Currency = 'AUD'
group by h.company_id,h.DivisionBuyer,country,datename(Month,date),currency
)as t1
group by company_id,DivisionBuyer,country,[Month], currency
order by country,company_id,[month],currency
May 18, 2016 at 11:54 pm
Currency conversion rates are not fixed, you know.
They change every day.
To pick the right rate for any particular invoice you need to have a table with conversion rates between all the pairs of currencies recorded for any particular day.
Do you have such a table?
_____________
Code for TallyGenerator
May 19, 2016 at 1:06 am
Yeah I just created a rate table, dbo.aus for the first set. trying to make it more dynamic so the rates can be updated and automatically and if there's match for that currency in the invoice table then convert them all to Australian Dollar.
Table dbo.aus
currency_name, shortname, rate
Singapore dollar, SGD, 1.00
American dollar, USD, 1.39
Danish Krone, DKK, .029
I have tried this cursor method, it runs but there's no output and the blank rows are in different sets.
DECLARE @currencytype VARCHAR
DECLARE currency_cursor CURSOR FOR
SELECT currency
FROM [ReadsoftSP_Extracts].[dbo].[AUS_Invoice_Header]
OPEN currency_cursor
FETCH NEXT FROM currency_cursor
INTO @currencytype
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @rate FLOAT
SET @rate = (select Rate from [ReadsoftSP_Extracts].dbo.AUD where Shortname = @currencytype)
select
Country
,h.DivisionBuyer
, h.company_id
,datename(Month,bccuploaddate) as [Month]
,count(h.invoiceid) as [Total Invoices]
, CASE WHEN Currency = @currencytype THEN (l.TotalInclGST * @rate) END AS [Total Inc GST]
, CASE WHEN Currency = @currencytype THEN replace(currency, @currencytype,'AUD') END AS [Currency]
from [dbo].[AUS_Invoice_Header] h
inner join [dbo].[Companies_BCC] c
on h.company_id = c.company_id
inner join [dbo].[AUS_Invoice_Line] l
on h.InvoiceID = l.InvoiceID
Where Currency = @currencytype
group by h.company_id,h.DivisionBuyer,country,datename(Month,date),currency, [TotalInclGST]
FETCH NEXT FROM currency_cursor
INTO @currencytype
END
CLOSE currency_cursor
DEALLOCATE currency_cursor
May 19, 2016 at 9:36 pm
I added a primary key to the AUD currency table and tried this. it returns the same as the cursor, multiple emtpy sets, can anyone detail what is wrong.
DECLARE @Row INT
DECLARE @index INT
SET @index = 1
SET @Row = (select Count(currency_name) from dbo.aud)
WHILE @index <= @Row
BEGIN
DECLARE @currencyname VARCHAR
DECLARE @rate FLOAT
SET @currencyname = (select shortname from [ReadsoftSP_Extracts].dbo.AUD where CID = @row)
SET @rate = (select Rate from [ReadsoftSP_Extracts].dbo.AUD where Shortname = @currencyname)
select
Country
,h.DivisionBuyer
, h.company_id
,datename(Month,bccuploaddate) as [Month]
,count(h.invoiceid) as [Total Invoices]
, CASE WHEN Currency = @currencyname THEN (l.TotalInclGST * @rate) END AS [Total Inc GST]
, CASE WHEN Currency = @currencyname THEN replace(currency, @currencyname,'AUD') END AS [Currency]
from [dbo].[AUS_Invoice_Header] h
inner join [dbo].[Companies_BCC] c
on h.company_id = c.company_id
inner join [dbo].[AUS_Invoice_Line] l
on h.InvoiceID = l.InvoiceID
Where Currency = @currencyname
group by h.company_id,h.DivisionBuyer,country,datename(Month,bccuploaddate),currency, [TotalInclGST]
END
May 19, 2016 at 11:44 pm
ringovski (5/19/2016)
Yeah I just created a rate table, dbo.aus for the first set. trying to make it more dynamic so the rates can be updated and automatically and if there's match for that currency in the invoice table then convert them all to Australian Dollar.Table dbo.aus
currency_name, shortname, rate
Singapore dollar, SGD, 1.00
American dollar, USD, 1.39
Danish Krone, DKK, .029
I have tried this cursor method, it runs but there's no output and the blank rows are in different sets.
First, your table dbo.aus must have a date range for each rate (or a single date when the rate is applied):
currency_name, shortname, date, rate
Singapore dollar, SGD, '20160520', 1.00
American dollar, USD, '20160520', 1.39
Danish Krone, DKK, '20160520', .029
Singapore dollar, SGD, '20160519', 1.00
American dollar, USD, '20160519', 1.37
Danish Krone, DKK, '20160519', .025
Second, you do not need to cursor through the currencies.
Use just a join, and apply rates only when the currency of the invoice is not the currency of the report (AUS).
DECLARE @ReportCurrency VARCHAR(10)
Set @ReportCurrency = 'AUS'
SET @rate = (select Rate from [ReadsoftSP_Extracts].dbo.AUD where Shortname = @currencytype)
select
Country
,h.DivisionBuyer
, h.company_id
,datename(Month,bccuploaddate) as [Month]
,count(h.invoiceid) as [Total Invoices]
, l.TotalInclGST * ISNULL(CC.rate, 1) AS [Total Inc GST]
, @ReportCurrency AS [Currency]
from [dbo].[AUS_Invoice_Header] h
LEFT JOIN dbo.aus CC ON CC.shortname = I.Currency and I.InvoiceDate = CC.Date
inner join [dbo].[Companies_BCC] c on h.company_id = c.company_id
inner join [dbo].[AUS_Invoice_Line] l on h.InvoiceID = l.InvoiceID
group by h.company_id,h.DivisionBuyer,country,datename(Month,date), [TotalInclGST]
Because "AUS" is not mentioned in dbo.AUS.shortname the would not be a join for AUS invoices,CC.Rate will be NULL, and it will be replaced with 1 by ISNULL in SELECT.
You may wish to have date ranges for the rates.
In this case create columns FromDate, ToDate insterad of Date, and use
LEFT JOIN dbo.aus CC ON CC.shortname = I.Currency and CC.From Date<=I.InvoiceDate and I.InvoiceDate <CC.ToDate
_____________
Code for TallyGenerator
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply