Solved

GET THE RIGHT EXCHANGE RATE FROM TABLE

Posted on 2012-12-27
40
383 Views
Last Modified: 2012-12-27
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.
0
Comment
Question by:emi_sastra
  • 20
  • 11
  • 3
  • +3
40 Comments
 
LVL 2

Expert Comment

by:harshada_sonawane
Comment Utility
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
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
-----------------------------------------------
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
0
 
LVL 17

Expert Comment

by:k_murli_krishna
Comment Utility
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;
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi harshada_sonawane,

Should check CurcyCode too.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi RehanYousaf,

Your code has null value result.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
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.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi  pratima_mcs,

Your code error here :

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.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
Comment Utility
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 

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Select TrsID ,Max(ExchangeDate),ExchangeRate
ExchangeRate

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

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi Saurv,

Your code not work for ExchangeDate < TrsDate

Thank you.
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
Hi  emi_sastra if you provide some sample data it will help rather than we assuming
0
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
Comment Utility
Can you post some sample data  for both tables and the output data-set   which you required as result...

that will be helpfull
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
I will provide my sample data.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Please see below attachment

Thank you.
TABLEA.xlsx
TABLEB.xlsx
0
 
LVL 17

Expert Comment

by:k_murli_krishna
Comment Utility
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.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Hi  k_murli_krishna,

Your code lack of group by and has null result.

Thank you.
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
Hi  emi_sastra you have posted the data thanks but what format you want your results in?
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
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

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
0
 
LVL 2

Expert Comment

by:harshada_sonawane
Comment Utility
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
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
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.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
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.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
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.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
@ harshada_sonawane and RehanYousaf

What if the selected ExchangeDate  has  ExchangeRate  = 0 ?

Thank you.
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
depends on what you want ... do you want to to keep 0 or look for value which is not 0?
0
 
LVL 5

Accepted Solution

by:
RehanYousaf earned 500 total points
Comment Utility
if you want to skip 0 then try the following code

-----------------------------------------------
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 ('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')
-----------------------------------------------


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

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
Since if the ExchangeRate  same as before there will no adding data for the same rate  until its rate  change.

Thank you.
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
For example the rate is change weekly, once a week.

Thank you.
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
Yes, my last query will work
0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
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.
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
you mean do something like

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

Author Comment

by:emi_sastra
Comment Utility
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.
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
-----------------------------------------------
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

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
What do you change at your code ?

Thank you.
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
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

Open in new window

0
 
LVL 1

Author Comment

by:emi_sastra
Comment Utility
You add

and e.ExchangeRate != 0 to your previous code, right ?

Thank you.
0
 
LVL 5

Expert Comment

by:RehanYousaf
Comment Utility
yes
0
 
LVL 1

Author Closing Comment

by:emi_sastra
Comment Utility
Hi RehanYousaf,

I think your code is perfect and your solution came the first and after revised to check the exchange rate. Thus the point is all yours.

Thank you very much for your help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now