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
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:
| DT | SECURITY_SEALS | NUM |
|---|---|---|
| 2024-09-23 00:00:00 | SEAL_XXX | 133 |
| 2024-09-24 00:00:00 | SEAL_XXX | 133 |
| 2024-09-25 00:00:00 | SEAL_XXX | 148 |
| 2024-09-23 00:00:00 | SEAL_YYY | null |
| 2024-09-24 00:00:00 | SEAL_YYY | 123 |
| 2024-09-25 00:00:00 | SEAL_YYY | 123 |
| 2024-09-23 00:00:00 | SEAL_ZZZ | 135 |
| 2024-09-24 00:00:00 | SEAL_ZZZ | 147 |
| 2024-09-25 00:00:00 | SEAL_ZZZ | 147 |