devxlogo

Finding the Correct Row Based on Effective Date

Finding the Correct Row Based on Effective Date

Question:
I have a table with three columns: the firstcontains the name of the currency, thesecond contains the currency rate and the third contains the actual date when thecurrency rate changed. They are in no particularorder, neither by currency, rate or date.My question is: how can I select a specific date and return the currency rate in effect for that date?

For example, the data might look like this:

    DEM   5.76   01/19/96    DKK   1.32   01/04/96    DEM   5.92   02/09/96    BEF   0.33   04/04/96    DEM   5.88   01/04/96    …   ….   ……..
How do I retrieve the actual currency rate forDeutche Mark, DEM, for January 20, 1996?(The answer should be 5.76.)

Answer:
Try this:

    SELECT value FROM Currencies        WHERE currency=’DEM’ AND        chg_date IN             (SELECT MAX(chg_date) FROM Currencies                WHERE chg_date 
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist