DEV Community πŸ‘©β€πŸ’»πŸ‘¨β€πŸ’»

Rolando GΓ³mez Tabar
Rolando GΓ³mez Tabar

Posted on • Updated on

Using Recursive Common Table Expressions to fill missing continuous data | Oracle SQL

Context

I have a table which contains the value of a currency in others currencies by date, but only working dates.
I need the data for every date since the begining of a source table, being that the missing data for a day should be the previous available one.

Sample data:
Source data
As you can see, the dates are not continuous.

First try:

WITH srct(fecha) AS (
    SELECT MIN(fecha) fecha FROM BCRD_TC_OTRAS_MON
     UNION ALL
    SELECT fecha + 1 fecha FROM srct WHERE fecha + 1 <= (SELECT MAX(fecha) fecha FROM BCRD_TC_OTRAS_MON)
)
SELECT a.fecha,
    NVL(dolar_can, LAG(dolar_can, 1) OVER(ORDER BY a.fecha)) dolar_can,
    NVL(franco_sui, LAG(franco_sui, 1) OVER(ORDER BY a.fecha)) franco_sui,
    NVL(euro, LAG(euro, 1) OVER(ORDER BY a.fecha)) euro,
    NVL(dolar_usd, LAG(dolar_usd, 1) OVER(ORDER BY a.fecha)) dolar_usd
FROM
    srct a
        LEFT JOIN
    BCRD_TC_OTRAS_MON b ON b.fecha = a.fecha
ORDER BY 1
Enter fullscreen mode Exit fullscreen mode

Results:
Data results try 1
This approach takes the data from previous date, but it does not work for more than 1 day of continuous missing data.
It could work if you use COALESCE and multiple LAG functions, but at the end, the query would not look elegant.

Second try:

WITH srct(fecha, dolar_can, franco_sui, euro, dolar_usd) AS (
    SELECT fecha, dolar_can, franco_sui, euro, dolar_usd FROM BCRD_TC_OTRAS_MON WHERE fecha = (SELECT MIN(fecha) fecha FROM BCRD_TC_OTRAS_MON)
     UNION ALL
    SELECT a.fecha + 1 fecha,
        NVL(b.dolar_can, a.dolar_can), NVL(b.franco_sui, a.franco_sui),
        NVL(b.euro, a.euro), NVL(b.dolar_usd, a.dolar_usd)
    FROM srct a
            LEFT JOIN
        BCRD_TC_OTRAS_MON b ON a.fecha + 1 = b.fecha
    WHERE a.fecha + 1 <= (SELECT MAX(fecha) fecha FROM BCRD_TC_OTRAS_MON)
)
SELECT fecha, dolar_can, franco_sui, euro, dolar_usd
  FROM srct
ORDER BY 1
Enter fullscreen mode Exit fullscreen mode

Results:
Final results
Using this approach you will get every date from srct and the previous available row data from the BCRD_TC_OTRAS_MON table for every missing date.

I hope it could help.

Thanks for reading.

Top comments (0)

🌚 Browsing with dark mode makes you a better developer.

It's a scientific fact.