CODEWITHSUNDEEP
sqloracle-databaseplsql
lonerangerdeveloper
lonerangerdeveloper

Reputation: 47

sql to replicate the latest record through the end of the month

I have a requirement to generate exchange rates through the end of the month based on the latest transaction for each currency.

For example, let's say we get 2 days worth of exchange rates to process (the file could bring x number of days worth of exch rates) and they look as follows:

04/25/2015,GBP,USD,1.8
04/25/2015,USD,GBP,1.25
04/26/2015,GBP,USD,1.7346
04/26/2015,USD,GBP,1.1357

In this example, I would need to take the two records on 4/26 and generate records through the end of the month so the output would need to look like this:

04/27/2015,GBP,USD,1.7346
04/28/2015,GBP,USD,1.7346
04/29/2015,GBP,USD,1.7346
04/30/2015,GBP,USD,1.7346
04/27/2015,USD,GBP,1.1357
04/28/2015,USD,GBP,1.1357
04/29/2015,USD,GBP,1.1357
04/30/2015,USD,GBP,1.1357

What I've done is the following in procedure in PLSQL but this doesn't seem to be working as I would expect it.

DECLARE 
 l_max_date date;

 CURSOR C1 IS
 SELECT         FROM_CURRENCY_DATE,
                FROM_CURRENCY,
                TO_CURRENCY, 
                NUMERATOR_BUY,
                CONVERSION_RATE,
                LEAD(conversion_rate) OVER (PARTITION BY from_currency ORDER BY from_currency_date) AS LEAD_conversion_rate,                
                ROW_NUMBER() OVER (PARTITION BY from_currency ORDER BY from_currency_date DESC) AS rn
        FROM    exchange_rate_staging_tbl
        WHERE   valid_flag is null or valid_flag <> 'E';

cur_rec c1%rowtype;

BEGIN 

 SELECT MAX(from_currency_date)
 INTO l_max_date
 FROM exchange_rate_staging_tbl;


 FOR cur_rec IN c1 LOOP

   if cur_rec.lead_conversion_rate is null then -->Null means it is the latest transaction
   dbms_output.put_line(cur_rec.from_currency||' '||
                       cur_rec.to_currency||' '||
                       'Inside IF'||' '||
                       cur_rec.conversion_rate);
   ELSE --> Records here are not the latest transaction but they still need to be inserted with their respective exch rate
   --dbms_output.put_line(l_max_date||last_day(l_max_date)); 
   dbms_output.put_line(cur_rec.from_currency||' '||
                       cur_rec.to_currency||' '||
                       'Inside Else'||' '||
                       cur_rec.conversion_rate);
   END IF;

  l_max_date := l_max_date+1; 
  END LOOP; 
 END;

The procedure above outputs:

GBP USD 25-APR-15 25-APR-15 Inside Else 1.8

GBP USD 26-APR-15 26-APR-15 Inside IF 1.7346

How would I generate the records based on the latest transaction for each currency through the end of the month in SQL or PLSQL.

Upvotes: 1

Views: 475

Answers (2)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

This query looks promising:

with 
  dm as (select max(from_currency_date) d from exchange_rate_staging_tbl),
  dr as (select distinct from_currency fc, to_currency tc,
      last_value(conversion_rate ignore nulls) over 
        (partition by from_currency, to_currency order by from_currency_date 
        rows between unbounded preceding and unbounded following) rate
    from exchange_rate_staging_tbl, dm where from_currency_date = dm.d),
  days as (select d+level-1 day from dm connect by d+level-1 <= last_day(d))
select days.day, dr.fc, dr.tc, rate
  from days cross join dr

SQLFiddle demo

There is one problem - if 26 Apr we have exchange rates for GBP/USD but USD/GBP is missing then only GBP/USD will be replicated. I don't know if this situation may take place, if yes then query has to be changed. There are also more doubts like what to do if there are two exchange rates for same currencies etc, but I don't know what you want in such situation.

How does it work:

  • first subquery dm just looks for max date in table, you may want to add filter for month here, for instance where from_currency_date <= 'date 2015-04-30',
  • dr gathers info for all currency rates from this day, distinct last_value guarantees that only one row will be taken for each pair of currencies,
  • days is hierarchical date generator, produces missing dates to the end of month,
  • final select joins dates and rates.

Edit according to comment:

How can I still bring the converstion rate for 4/25

with 
  dm as (select date '2015-04-25' d from dual), 
  dc as (
    select distinct from_currency, to_currency
      from exchange_rate_staging_tbl, dm 
      where from_currency_date between d and last_day(d)),
  days as (
    select d+level-1 from_currency_date 
      from dm connect by d+level-1 <= last_day(d))
select from_currency_date, from_currency, to_currency,
    last_value(conversion_rate ignore nulls) over 
      (partition by from_currency, to_currency order by from_currency_date 
      rows unbounded preceding ) rate 
  from dc cross join days 
  left join exchange_rate_staging_tbl e
    using (from_currency_date, from_currency, to_currency)  
  order by from_currency_date, from_currency, to_currency

SQLFiddle demo

This query shows data from given date (you define it in first line) to the end of the month filling gaps - let's say we have no conversion rates for Saturday and Sunday, query fills this gap with data from Friday.

Function last_value() finds last value of conversion rate from some set of values somehow grouped (partitioned). In our case these partitions are pairs of currencies, order is currency_date. In human words: find last not null value of conversion rate for each pair of currencies in given period. Note that this period changes for each row. Last_value is one of analytic functions which have very broad range of use. Good article about these functions: Analytic functions by Example.

Upvotes: 0

TommCatt
TommCatt

Reputation: 5636

Your data doesn't look like any rate data I've ever seen. Usually, everything is referenced against one currency. In my case, it was USD. Now you need only one entry for each currency per day. Like this:

Effective  Code Factor
2015-05-02 GBP  0.662251656 --based on today's rate of 1.51 GBP->USD

To go from USD to GBP: USD * factor = GBP
To go from GBP to USD: GBP / factor = USD

There is a slide presentation that shows such a table design here. The currency discussion starts on slide/page 12, the query on page 16. The table design is simple:

create table XRates(
    Code       char( 3 ) not null,
    Effective  date not null,
    Factor     decimal( 12, 9 ) not null
    constraint PK_XRates primary key( Code, Effective )
);

A couple of nice features about the design is that you maintain historical rates in the same table and daily entries are not necessary. An entry is made only when the rate changes. The query returns the rate that was in effect on the given date, even if that rate was established by a week-old entry (not likely in today's currency markets).

Upvotes: 0

Related Questions