I have 2 tables: DDates

Currancy:

How can I get result like this?

I guess I should use LAG function, but it's not working.

The current attempt is

SELECT t.ddate,
       COALESCE(c.curr,LAG(c.curr) OVER(PARTITION BY c.curr ORDER BY c.ddate))
  FROM ddate t
  LEFT JOIN Currancy c
    ON t.ddate = c.ddate
 ORDER BY c.curr, c.ddate DESC;

CodePudding user response：

You can use a partitioned outer join and then get the latest value with the LAST_VALUE analytic function:

SELECT d."DATE",
       c.curr,
       LAST_VALUE(c.rate) IGNORE NULLS OVER (PARTITION BY c.curr ORDER BY d."DATE")
         AS rate
FROM   ddates d
       LEFT OUTER JOIN currancy c
       PARTITION BY (c.curr)
       ON (d."DATE" = c."DATE");

Which, for the sample data:

CREATE TABLE DDates ("DATE") AS
SELECT DATE '2022-01-05' FROM DUAL UNION ALL
SELECT DATE '2022-01-04' FROM DUAL UNION ALL
SELECT DATE '2022-01-03' FROM DUAL UNION ALL
SELECT DATE '2022-01-02' FROM DUAL UNION ALL
SELECT DATE '2022-01-01' FROM DUAL;

CREATE TABLE Currancy ("DATE", curr, rate) AS
SELECT DATE '2022-01-05', 'USD', 12 FROM DUAL UNION ALL
SELECT DATE '2022-01-04', 'USD', NULL FROM DUAL UNION ALL
SELECT DATE '2022-01-03', 'EUR', 11 FROM DUAL UNION ALL
SELECT DATE '2022-01-01', 'USD', 10 FROM DUAL UNION ALL
SELECT DATE '2022-01-05', 'EUR', 13 FROM DUAL UNION ALL
SELECT DATE '2022-01-04', 'EUR', 14 FROM DUAL UNION ALL
SELECT DATE '2022-01-01', 'EUR', 13 FROM DUAL;

Which outputs:

fiddle