Put data from previous date in a date that doesn't have data

don’t really know how to explain this in english so I’m just going to put the data that I have and what I want to show. This is in ORACLE btw.

The data I have is:

Date            SECURITY_SEALS   NUM     PREVIOUS_DATE   PREVIOUS_NUM
23/09/2024      SEAL_XXX         133        
25/09/2024      SEAL_XXX         148     23/09/2024      133

Where Date is just a date to see how many SECURTIY_SEALS we had stored.
SECURITY_SEALS is like the name of a "product"
NUM is the count/sum of units of that specific product
PREVIOUS_DATE is the previous date obtained by the LAG function
PREVIOUS_NUM is the previous num obtained by the LAG function

I have another table with all Dates for a period of time (around 1 year), but I want to focus in these dates to see how it works and scale it later.

In that Dates table I just have the dates:

23/09/2024
24/09/2024
25/09/2024

What I want to do is get the data for every date, and when I don’t have records from the day, take the previous day data.

This should be the end result:

Date            SECURITY_SEALS   NUM     
23/09/2024      SEAL_XXX         133    
24/09/2024      SEAL_XXX         133 (this would be the same SECURITY_SEALS and NUM data from previous day since I have no original information for the day 24/09/2024)
25/09/2024      SEAL_XXX         148    

How do I put the info in the 24/09/2024 record?

I’ve tried all sorts of joins and unions and prepared the information with LAGs to make it easier but I can’t seem to solve it

>Solution :

You can use a PARTITIONed OUTER JOIN and the LAST_VALUE analytic function:

SELECT d.dt,
       s.security_seals,
       LAST_VALUE(s.num) IGNORE NULLS OVER (
         PARTITION BY s.security_seals ORDER BY d.dt
       ) AS num
FROM   dates d
       LEFT OUTER JOIN seals s
       PARTITION BY (s.security_seals)
       ON (d.dt = s.dt);

Which, for the sample data:

CREATE TABLE dates (dt) AS
SELECT DATE '2024-09-23' FROM DUAL UNION ALL
SELECT DATE '2024-09-24' FROM DUAL UNION ALL
SELECT DATE '2024-09-25' FROM DUAL;

CREATE TABLE seals (Dt, SECURITY_SEALS, NUM) AS
SELECT DATE '2024-09-23', 'SEAL_XXX', 133 FROM DUAL UNION ALL
SELECT DATE '2024-09-25', 'SEAL_XXX', 148 FROM DUAL UNION ALL
SELECT DATE '2024-09-24', 'SEAL_YYY', 123 FROM DUAL UNION ALL
SELECT DATE '2024-09-23', 'SEAL_ZZZ', 135 FROM DUAL UNION ALL
SELECT DATE '2024-09-24', 'SEAL_ZZZ', 147 FROM DUAL;

Outputs:

fiddle