Technical Article

Get the Latest Currency Rate as at a date

,

Have you faced a situation where you want to have the latest currency rates available upto a particular date. Normally you may have to run sub queries but derived tables is the easiest way to do.

If Exists (Select * from Sysobjects Where Name ='CurrencyRates' and ObjectProperty(ID, 'IsUserTable')=1)
Drop table CurrencyRates

If Exists (Select * from Sysobjects Where Name ='Currency' and ObjectProperty(ID, 'IsUserTable')=1)
Drop table Currency

Create Table Currency
(
Curr_Code varchar(4) NOT NULL Primary Key,
Curr_Name  varchar(60) NULL
)

GO 

Create Table CurrencyRates
(
Curr_Code varchar(4) NOT NULL ,
Curr_Date  Datetime NOT NULL,
Curr_Rate Decimal(18,4) NOT NULL,
Constraint PK_Key_CurrencyRates Primary KEY (Curr_Code, Curr_Date),
Constraint FK_Key Foreign Key (Curr_Code) 
References Currency(curr_Code) 
)

Insert Currency Values('LKRS', 'Sri Lankan Rupees')
Insert Currency Values('US $', 'U.S. Dollers')
Insert Currency Values('YEN', 'Japaneese Yen')
Insert Currency Values('UK £', 'U.K. Pounds')

Insert CurrencyRates Values('LKRS', '01 Jan 2000', 1.000)

Insert CurrencyRates Values('YEN', '17 Jan 2000', .6325)
Insert CurrencyRates Values('YEN', '15 Jan 2000', .6425)
Insert CurrencyRates Values('YEN', '20 Jan 2000', .6375)
Insert CurrencyRates Values('YEN', '02 Jan 2000', .6525)
Insert CurrencyRates Values('YEN', '14 Feb 2000', .6455)
Insert CurrencyRates Values('YEN', '13 Mar 2000', .7125)
Insert CurrencyRates Values('YEN', '30 Mar 2000', .6815)

Insert CurrencyRates Values('US $', '12 Jan 2000', 79.5)
Insert CurrencyRates Values('US $', '18 Jan 2000', 82.5)
Insert CurrencyRates Values('US $', '20 Jan 2000', 80.25)
Insert CurrencyRates Values('US $', '01 Jan 2000', 79.25)
Insert CurrencyRates Values('US $', '12 Feb 2000', 84.5)
Insert CurrencyRates Values('US $', '10 Mar 2000', 78.5)
Insert CurrencyRates Values('US $', '31 Mar 2000', 81.5)


Select A.Curr_code, c.Curr_Name, A.Curr_Date, B.Curr_Rate 
From (select Curr_Code, max(Curr_Date) Curr_Date 
From CurrencyRates 
Where Curr_Date <= '10 Jan 2000' --Or any date specified 
Group BY Curr_Code ) A, CurrencyRates B, Currency C
Where A.Curr_Code= B.Curr_Code and A.curr_Date= B.Curr_Date and A.Curr_Code= C.Curr_Code

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating