# GET THE RIGHT EXCHANGE RATE FROM TABLE

Hi All,

I have transaction table and a exchange rate table.

I. TableA
1. TrsId
2. TrsDate
3. CurcyCode

II. TableB
1. CurcyCode
2. ExchangeDate
3. ExchangeRate

My problem is how to get the right exchange rate for every transaction ?

Scenario :

1. If TrsDate  = ExchangeDate  then get the ExchangeRate.
2. If TrsDate > ExchangeDate  then get the latest ExchangeDate before the TrsDate's  ExchangeRate.

How could I do it ?

Thank you.

try this:

select TrsDate  ,(select top(1) ExchangeDate  from TableB b where b.ExchangeDate  <=a.TrsDate  order by b.ExchangeDate  desc) ExchangeDate
from TableA a
-----------------------------------------------
DECLARE @Transaction TABLE (
TrsID INT IDENTITY (1,1)
,TrsDate DATETIME
,CurcyCode VARCHAR(20)
)

INSERT INTO @Transaction VALUES ('2012-12-04', 'PKR')
INSERT INTO @Transaction VALUES ('2012-12-03', 'USD')
INSERT INTO @Transaction VALUES ('2012-12-07', 'PKR')
INSERT INTO @Transaction VALUES ('2012-12-08', 'USD')
INSERT INTO @Transaction VALUES ('2012-12-09', 'USD')

-----------------------------------------------
DECLARE @Exchange TABLE (
CurcyCode VARCHAR(20)
,ExchangeDate DATETIME
,ExchangeRate MONEY
)

INSERT INTO @Exchange VALUES ('PKR', '2012-12-01', '150.00')
INSERT INTO @Exchange VALUES ('USD', '2012-12-02', '1.61')
INSERT INTO @Exchange VALUES ('USD', '2012-12-03', '1.59')
INSERT INTO @Exchange VALUES ('PKR', '2012-12-04', '153.50')
INSERT INTO @Exchange VALUES ('PKR', '2012-12-05', '155.00')
INSERT INTO @Exchange VALUES ('USD', '2012-12-06', '1.65')
INSERT INTO @Exchange VALUES ('PKR', '2012-12-10', '157.50')
INSERT INTO @Exchange VALUES ('USD', '2012-12-11', '1.75')
-----------------------------------------------

SELECT
*
,(
SELECT TOP 1 e.ExchangeRate
FROM @Exchange e
WHERE e.CurcyCode = t.CurcyCode AND e.ExchangeDate <= t.TrsDate
ORDER BY e.ExchangeDate DESC
) AS ExchangeRate
FROM
@Transaction t
Try this query and get back if you face any problem:

SELECT       B.ExchangeRate,
TOP 1 CASE WHEN A.TrsDate  > B.ExchangeDate
THEN B.ExchangeRate
END
FROM             TableA A
INNER JOIN       TableB B
ON       A.CurcyCode = B.CurcyCode
GROUP BY B.ExchangeRate
ORDER BY B.ExchangeDate DESC;

Should check CurcyCode too.

Thank you.

Hi RehanYousaf,

Your code has null value result.

Thank you.

Hi k_murli_krishna,

Your code get the below message :

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'TOP'.

Thank you.
Select TrsID ,ExchangeRate
From TableA A inner join TableB B on A.CurcyCode=B.CurcyCode
where TrsDate = ExchangeDate

Union

Select X.TrsID ,ExchangeRate
From (

Select TrsID ,Max(ExchangeDate),ExchangeRate
ExchangeRate
From TableA A inner join TableB B on A.CurcyCode=B.CurcyCode
where TrsDate > ExchangeDate
and TrsID not in (
Select TrsID ,ExchangeRate
From TableA A inner join TableB B on A.CurcyCode=B.CurcyCode
where TrsDate = ExchangeDate) ) X

Hi  pratima_mcs,

TrsID not in (
Select TrsID ,ExchangeRate
From TableA A inner join TableB B on A.CurcyCode=B.CurcyCode
where TrsDate = ExchangeDate)

select clause more than columns (Select TrsID ,ExchangeRate)

Thank you.
Select TrsID ,ExchangeRate
From TableA A inner join TableB B on A.CurcyCode=B.CurcyCode
where TrsDate = ExchangeDate

Union

Select X.TrsID ,ExchangeRate
From (

Select TrsID ,Max(ExchangeDate),ExchangeRate
ExchangeRate
From TableA A inner join TableB B on A.CurcyCode=B.CurcyCode
where TrsDate > ExchangeDate
and TrsID not in (
Select TrsID
From TableA A inner join TableB B on A.CurcyCode=B.CurcyCode
where TrsDate = ExchangeDate) ) X
try like this....

``````Declare  @TableA table (TrsId int,TrsDate datetime, CurcyCode int)

Declare @TableB table (CurcyCode int,ExchangeDate datetime,ExchangeRate int)

insert into @tablea
select 1,getdate(),10
union all
select 2,getdate()-1,11

insert into @tableb
select 10,getdate(),100
union all
select 11,getdate()-3,200
union all
select 11,getdate()-4,220
union all
select 10,getdate()-1,90

select * from @tablea
select * from @tableb

select t1.*,ISNULL(t2.ExchangeRate,x.ExchangeRate) as ExchangeRate  from @tablea t1
left join @TableB t2 on t1.CurcyCode=t2.CurcyCode and t1.TrsDate=t2.ExchangeDate
outer apply ( select top(1) ExchangeRate from @tableB where ExchangeDate < t1.TrsDate order by  ExchangeDate desc  ) x ``````

Select TrsID ,Max(ExchangeDate),ExchangeRate
ExchangeRate

When we use max() should have group by.
It has double ExchangeRate.

Thank you.

Hi Saurv,

Your code not work for ExchangeDate < TrsDate

Thank you.
Hi  emi_sastra if you provide some sample data it will help rather than we assuming
Can you post some sample data  for both tables and the output data-set   which you required as result...

I will provide my sample data.

Thank you.

Thank you.
TABLEA.xlsx
TABLEB.xlsx
It is very difficult to get it in a single query since one situation will fetch multiple rows and the second one a single row. The problem can be solved in two different queries as follows:

Query 1: If TrsDate  = ExchangeDate  then get the ExchangeRate.

SELECT CASE WHEN A.TrsDate  = B.ExchangeDate
THEN B.ExchangeRate
END
FROM             TableA A
INNER JOIN       TableB B
ON       A.CurcyCode = B.CurcyCode;

Query 2: If TrsDate > ExchangeDate  then get the latest ExchangeDate before the TrsDate's  ExchangeRate.

SELECT TOP 1 CASE WHEN A.TrsDate  > B.ExchangeDate
THEN B.ExchangeRate
END
FROM             TableA A
INNER JOIN       TableB B
ON       A.CurcyCode = B.CurcyCode
GROUP BY B.ExchangeRate
ORDER BY B.ExchangeDate DESC;

You can try adding more columns in both query's SELECT list if you require any.

For obtaining in a single query, you can try:

SELECT CASE WHEN A.TrsDate  = B.ExchangeDate
THEN B.ExchangeRate
END
FROM             TableA A
INNER JOIN       TableB B
ON       A.CurcyCode = B.CurcyCode
UNION
SELECT TOP 1 CASE WHEN A.TrsDate  > B.ExchangeDate
THEN B.ExchangeRate
END
FROM             TableA A
INNER JOIN       TableB B
ON       A.CurcyCode = B.CurcyCode
GROUP BY B.ExchangeRate
ORDER BY B.ExchangeDate DESC;

The SELECT list in UNION should be of same number and type of columns.

Hi  k_murli_krishna,

Your code lack of group by and has null result.

Thank you.
Hi  emi_sastra you have posted the data thanks but what format you want your results in?
I have modified my code slightly ... If you get any Nulls can you send me the specific TrsDate and CurcyCode ... as it will help debug

``````-----------------------------------------------
DECLARE @Transaction TABLE (
TrsID INT IDENTITY (1,1)
,TrsDate VARCHAR(20)
,CurcyCode VARCHAR(20)
)

INSERT INTO @Transaction VALUES ('2012-11-04', 'GBP')
INSERT INTO @Transaction VALUES ('2012-12-04', 'GBP')
INSERT INTO @Transaction VALUES ('2012-12-03', 'USD')
INSERT INTO @Transaction VALUES ('2012-12-07', 'GBP')
INSERT INTO @Transaction VALUES ('2012-12-08', 'USD')
INSERT INTO @Transaction VALUES ('2012-12-09', 'USD')

-----------------------------------------------
DECLARE @Exchange TABLE (
CurcyCode VARCHAR(20)
,ExchangeDate VARCHAR(20)
,ExchangeRate MONEY
)

INSERT INTO @Exchange VALUES ('GBP', '2012-12-01', '15000')
INSERT INTO @Exchange VALUES ('USD', '2012-12-02', '9161')
INSERT INTO @Exchange VALUES ('USD', '2012-12-03', '9159')
INSERT INTO @Exchange VALUES ('GBP', '2012-12-04', '15350')
INSERT INTO @Exchange VALUES ('GBP', '2012-12-05', '15500')
INSERT INTO @Exchange VALUES ('USD', '2012-12-06', '9165')
INSERT INTO @Exchange VALUES ('GBP', '2012-12-10', '15750')
INSERT INTO @Exchange VALUES ('USD', '2012-12-11', '9175')
-----------------------------------------------

SELECT
*
,ISNULL((
SELECT TOP 1 e.ExchangeRate
FROM @Exchange e
WHERE e.CurcyCode = t.CurcyCode AND CONVERT(DATETIME, e.ExchangeDate) <= CONVERT(DATETIME, t.TrsDate )
ORDER BY CONVERT(DATETIME, e.ExchangeDate) DESC
), 0) AS ExchangeRate
FROM
@Transaction t``````
Select TrsID ,ExchangeRate
From TableA A inner join TableB B on A.CurcyCode=B.CurcyCode
where TrsDate = ExchangeDate

Union

Select X.TrsID ,B2.ExchangeRate
From (

Select TrsID ,Max(ExchangeDate)
ExchangeRate
From TableA A inner join TableB B on A.CurcyCode=B.CurcyCode
where TrsDate > ExchangeDate
and TrsID not in (
Select TrsID
From TableA A inner join TableB B on A.CurcyCode=B.CurcyCode
where TrsDate = ExchangeDate)
Group by TrsID
) X  inner join TableB B2 on X.TrsID = B2.TrsID
try this:

select TrsDate  ,(select top(1) ExchangeDate  from TableB b where b.ExchangeDate  <=a.TrsDate and a.CurcyCode=b.CurcyCode  order by b.ExchangeDate  desc) ExchangeDate
from TableA a

H pratima_mcs,

- inner join TableB B2 on X.TrsID = B2.TrsID
B2 has no TrsId column.

- Select TrsID ,Max(ExchangeDate)
ExchangeRate
Why Max(ExchangeDate) to ExchangeRate ?

Thank you.

Hi All,

It seems that harshada_sonawane code solve the problem.

I've just tested at a glance.

Any idea of the code, is it perfect ?

Thank you.

Hi All,

It seems that harshada_sonawane and RehanYousaf code solve the problem.

I've just tested at a glance.

Any idea of the code, is it perfect ?

Thank you.

What if the selected ExchangeDate  has  ExchangeRate  = 0 ?

Thank you.
depends on what you want ... do you want to to keep 0 or look for value which is not 0?
RehanYousaf

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.

Since if the ExchangeRate  same as before there will no adding data for the same rate  until its rate  change.

Thank you.

For example the rate is change weekly, once a week.

Thank you.
Yes, my last query will work

Try update ExchangeRate  to 0
for USD
where month = 6 and day between 12 and 15

Then run your query. What are the result ?

Thank you.
you mean do something like

INSERT INTO @Exchange VALUES ('USD', '2012-06-15', '0')

update TableB
set ExchangeRate  = 0
WHERE CURCYCODE = 'USD'
AND MONTH(ExchangeDate) = 6
and day(ExchangeDate) between 12 and 15

Run the above command and test your query to see your result.

Thank you.
``````-----------------------------------------------
DECLARE @Transaction TABLE (
TrsID INT IDENTITY (1,1)
,TrsDate VARCHAR(20)
,CurcyCode VARCHAR(20)
)

INSERT INTO @Transaction VALUES ('2012-11-04', 'GBP')
INSERT INTO @Transaction VALUES ('2012-12-04', 'GBP')
INSERT INTO @Transaction VALUES ('2012-12-03', 'USD')
INSERT INTO @Transaction VALUES ('2012-12-07', 'GBP')
INSERT INTO @Transaction VALUES ('2012-12-08', 'USD')
INSERT INTO @Transaction VALUES ('2012-12-09', 'USD')

-----------------------------------------------
DECLARE @Exchange TABLE (
CurcyCode VARCHAR(20)
,ExchangeDate VARCHAR(20)
,ExchangeRate MONEY
)

INSERT INTO @Exchange VALUES ('GBP', '2012-11-01', '14000')
INSERT INTO @Exchange VALUES ('USD', '2012-06-14', '6000') -- New
INSERT INTO @Exchange VALUES ('GBP', '2012-12-01', '15000')
INSERT INTO @Exchange VALUES ('USD', '2012-12-02', '9161')
INSERT INTO @Exchange VALUES ('USD', '2012-12-03', '9159')
INSERT INTO @Exchange VALUES ('GBP', '2012-12-04', '0')
INSERT INTO @Exchange VALUES ('GBP', '2012-12-05', '15500')
INSERT INTO @Exchange VALUES ('USD', '2012-12-06', '9165')
INSERT INTO @Exchange VALUES ('GBP', '2012-12-10', '15750')
INSERT INTO @Exchange VALUES ('USD', '2012-12-11', '9175')
-----------------------------------------------

UPDATE
@Exchange
SET
ExchangeRate  = 0
WHERE
CURCYCODE = 'USD'
AND MONTH(ExchangeDate) = 6
AND DAY(ExchangeDate) BETWEEN 12 AND 15

SELECT * FROM @Exchange

SELECT
*
,ISNULL((
SELECT TOP 1 e.ExchangeRate
FROM @Exchange e
WHERE e.CurcyCode = t.CurcyCode AND CONVERT(DATETIME, e.ExchangeDate) <= CONVERT(DATETIME, t.TrsDate ) AND e.ExchangeRate != 0
ORDER BY CONVERT(DATETIME, e.ExchangeDate) DESC
), 0) AS ExchangeRate
FROM
@Transaction t``````

What do you change at your code ?

Thank you.
I added the follwoing script as you requested

``````UPDATE
@Exchange
SET
ExchangeRate  = 0
WHERE
CURCYCODE = 'USD'
AND MONTH(ExchangeDate) = 6
AND DAY(ExchangeDate) BETWEEN 12 AND 15

SELECT * FROM @Exchange``````