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:
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
Results:
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
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)