1

I have a table with transactions with different currencies, and some of these dates fall on a weekend or holiday. Example:

Date       currency_code    ammount
20-02-2017 EUR              55
18-02-2017 GBP              33
17-02-2017 EUR              44.55

An example of what my Currency table looks like:

SELECT rate,date, currency_code FROM exchangeTable:

rate      Date          currency_code
53,35     13-02-2017    ADP   
53,35     14-02-2017    ADP    
182,4     16-02-2017    ADP    
192,45    17-02-2017    ADP    
191,31    20-02-2017    ADP

Is there a simple subquery I can use in my join statement which will join the most recent currency date onto my transaction date, if it falls on a weekend or holiday? I think I should use a partition here, but don't have much experience with them.

left join (?????????) a on a.date = b.date and a.currency_code= b.currency_code
David
  • 2,298
  • 6
  • 22
  • 56
Kris
  • 13
  • 6

2 Answers2

0

You can solve this using a derived table, ROW_NUMBER, and partitions. What this does is eliminate weekends using the DATEPART function with the dw (day of week) argument to ignore any Saturday and Sunday. For holidays, you'll have to have a table of holiday dates since holidays are entirely subjective.

ROW_NUMBER allows you to get an index of the row number given a custom ordering and partition. We partition by currency so the index resets every time we hit a new currency, and we order by DATE DESC so the most recent date for each currency is 1.

-- create a sample table with the date, currency, and exchange rate
create table rates (
    id int identity(1,1) primary key,
    date date not null,
    currency char(3) not null,
    rate decimal(10,2) not null
)
go

-- create table of holidays we'll use for excluding rates records later
create table holidays (
    id int identity(1, 1) primary key,
    date date not null,
    name varchar(100) not null
)

-- create some sample data
-- Feb 18 and 19 are Saturday and Sunday
insert into rates (date, currency, rate) values
('2017-02-16', 'GBP', 1.23),
('2017-02-17', 'GBP', 1.24),
('2017-02-18', 'GBP', 1.25),
('2017-02-19', 'GBP', 1.26),
('2017-02-20', 'GBP', 1.27),
('2017-02-16', 'SGD', 2.23),
('2017-02-17', 'SGD', 2.24),
('2017-02-18', 'SGD', 2.25),
('2017-02-19', 'SGD', 2.26),
('2017-02-20', 'SGD', 2.27);

insert into holidays (date, name) values
('2017-02-20', 'National Cherry Pie Day'); -- this is a real thing


with t as (
    select id,
           date,
           currency,
           rate,
           row_number() over (partition by currency order by date desc) as age
    from   rates
    where  datepart(dw, date) not in (1, 7) -- sunday, saturday
           and date not in (select date from holidays) -- exclude holiday rates
)
select * from t where age = 1;
mroach
  • 2,403
  • 1
  • 22
  • 29
0

First, you need to JOIN all rows form the Currency table with the same CurrencyCode, then you can use the RANK() PARTITION (... function to select the one with the most recent date compared to the transaction's date.

SELECT
    *
FROM
    (SELECT
        t.*,
        c.*,
        RANK() OVER (PARTITION BY t.ID ORDER BY ABS(DATEDIFF(d, t.[Date], c.[Date])) ASC, c.[Date] DESC) rn
    FROM
        Transactions t
    INNER JOIN
        Currency c ON t.CurrencyCode = c.CurrencyCode) t
WHERE
    t.rn = 1
banazs
  • 116
  • 3