Inserting values to NULL rows based on the closest date that is not NULL and is earlier than the row date

user_4515374 0
Viewed 67

I'm getting foreign exchange rates from Riksbanken API. This API doesn't give values for holidays, saturdays or sundays. So my FX rates is empty for those days. However when I convert currencies in my tables I do a join based on date & currency. So in a case where I have a transaction on a Sunday then I can't convert it because that is NULL, and transaction amount * NULL = NULL.

Basically this is my situation:

DECLARE @fxrates TABLE(cur varchar(5), rate decimal, rowDate date);
INSERT INTO @fxrates VALUES
 ('EUR',10.40,'2020-04-30')
, ('EUR',10.50,'2020-05-01')
, ('EUR',NULL,'2020-05-02')
, ('EUR',NULL,'2020-05-03')
, ('EUR',10.60,'2020-05-04')
, ('EUR',10.70,'2020-05-05')


DECLARE @value TABLE(cur varchar(5), amount decimal, rowDate date);
INSERT INTO @value VALUES
 ('EUR',1500,'2020-04-30')
, ('EUR',9000,'2020-05-01')
, ('EUR',1000,'2020-05-02')
, ('EUR',300,'2020-05-03')
, ('EUR',160,'2020-05-04')
, ('EUR',170,'2020-05-05')


--How I convert the values
select v.amount * fx.rate as [Converted amount] from @fxrates fx 
JOIN @value v
on fx.cur=v.cur
and fx.rowDate=v.rowDate

My solution for this would be to always replace NULL with the "earlier" value, based on date. However I have no idea how that logic would be implemented in SQL. So my fxrates table would look like this:

 ('EUR',10.40,'2020-04-30')
 ('EUR',10.50,'2020-05-01')
 ('EUR',10.50,'2020-05-02')
 ('EUR',10.50,'2020-05-03')
 ('EUR',10.60,'2020-05-04')
 ('EUR',10.70,'2020-05-05')

Would replacing the NULL values be the best approach moving forward? And how can I achieve that using SQL?

insert date tsql sql-server
2 Answers

I think you can use CROSS APPLY to get the latest exchange rate that is valid before or at the transaction date.

SELECT v.amount * fx.rate AS [Converted amount]
       FROM @value v
            CROSS APPLY (SELECT TOP 1
                                fx.rate
                                FROM @fxrates fx
                                WHERE fx.cur = v.cur
                                      AND fx.rowdate <= v.rowdate
                                      AND fx.rate IS NOT NULL
                                ORDER BY fx.rowdate DESC) fx;
user_96614240

You could use a correlated subquery here to find the most recent non NULL price for every date which be missing a price:

SELECT
    cur,
    COALESCE(rate,
        (SELECT TOP 1 f2.rate FROM @fxrates f2
         WHERE f2.cur = f1.cur AND f2.rowDate < f1.rowDate AND f2.rate IS NOT NULL
         ORDER BY f2.rowdate DESC)
    ) AS rate,
    rowDate
FROM @fxrates f1
ORDER BY
    cur,
    rowDate;

screen capture of demo below

Demo

user_18632290
Related