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