Need SQL Query to get latest CurrencyRate

Question

Post reply

Need SQL Query to get latest CurrencyRate

saurabh.deshpande

SSCommitted

Points: 1738
More actions
December 17, 2010 at 7:20 am

#100747

Hi
I have a transaction table (Invoices) and a CurrencyTable. In Currency table Currency Exchnage Rates are available with Exchange Date.
In transaction table there two columns (InvoicePrice, InvoiceCurrency).
InvoicePrice store InvoiceAmt and InvoiceCurrency column store currency in which Invoice generated. I need to convert from InvoiceCurrency to USD (US Dollar) and for that I have use Currency Table.
There are mulpitle Exchnage Rates are available in Currency Table with respective Exchange Date.
I have to pick up the latest Exchnage Rate according to Invoice Date.
For Example see below
Currency Table
ExchnageRate, ExchnageDate,FromCurr, ToCurr
3.45, 1-Jan-2010, EUR, USD
3.54, 15-Jan-2010, EUR, USD
Invoices
1st Transaction
-----------------
InvoiceDate,InvoiceCurrency,InvoiceAmt
12-Jan-2010,EUR, 500,
This Transaction should use the Exchnage Rate of Date 1-Jan-2010
2nd Transaction
---------------------
InvoiceDate,InvoiceCurrencyInvoiceAmt,
18-Jan-2010,EUR, 900,
This Transaction should use the Exchnage Rate of Date 15-Jan-2010
Can anyone suggest what SQL query I need write to achive above logic.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

If you provide table script and sample data in a more consumable format, I'll be glad to help.

Take a look at the article linked in my signature line.

-- Gianluca Sartori

WayneS SSC Guru Points: 95461 More actions · Answer 2

This should give you an idea for how to do this.

If this doesn't work, please follow the advice given by Gianluca.

-- See how you start off by actually creating a table and then

-- inserting the data into it? Your doing this makes it a lot easier

-- for all of us volunteers to help you. So, help us help you.

-- See http://www.sqlservercentral.com/articles/Best+Practices/61537/

-- for more details on how to do all of this.

DECLARE @Currency Table (ExchangeRate numeric(5,2), ExchangeDate datetime, FromCurr char(3), ToCurr char(3));

INSERT INTO @Currency

SELECT 3.45, '1-Jan-2010', 'EUR', 'USD' UNION ALL

SELECT 3.54, '15-Jan-2010', 'EUR', 'USD';

DECLARE @Invoices TABLE (InvoiceDate datetime, InvoiceCurrency char(3), InvoiceAmt numeric(5,2));

INSERT INTO @Invoices SELECT '12-Jan-2010', 'EUR', 500;

-- 1. Get the latest ExchangeDate where the date is <= the InvoiceDate

-- and it is for the same currency.

-- 2. Then get the exchange rate for that date.

-- These are known as correlated subqueries.

SELECT i.*, -- get the invoice table fields

(SELECT ExchangeRate

FROM @Currency c1

WHERE c1.FromCurr = i.InvoiceCurrency

AND c1.ExchangeDate = (SELECT MAX(ExchangeDate)

FROM @Currency c2

WHERE c2.FromCurr = i.InvoiceCurrency

AND c2.ExchangeDate <= i.InvoiceDate)) ExchangeRate

FROM @Invoices i;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2