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 
A short but interesting article, the author has figured out a way to create a PDF from a stored procedure without using a third party library.
2019-09-20 (first published: 2003-08-26)
72,112 reads
Robert is back this week with a look at the text data type. Not the easiest thing to work with, but sometimes a varchar just doesn't give you the range you need.
2003-05-07
26,988 reads
Fast Track to MDX gives readers all the necessary background information needed to write useful, powerful MDX expressions and introduces the most frequently used MDX functions and constructs. No prior knowledge is assumed and examples are used throughout the book to rapidly develop MDX skills to the point where a reader can solve real business problems. A CD containing examples from within the book, and a time-limited version of ProClarity, is included
2003-05-01
2,698 reads
Robert is our expert on dynamic sql. This week he offers some good hints for planning the contruction of a proc that will use dynamic sql. He also adds some suggestions on how to format the code so that when you return to it later, you can figure out what you were doing!
2007-10-02 (first published: 2003-04-25)
50,228 reads
In this article Andy looks at one way you can separate your presentation code from your data when you generate HTML tables for simple reports. Lots of code examples so you can see how it works.
2002-08-05
5,904 reads