Kjetil Kjetil - 16 days ago 5
SQL Question

SQL UNION ALL only include newer entries from 'bottom' table

Fair warning: I'm new to using SQL. I do so on an Oracle server either via AQT or with SQL Developer.

As I haven't been able to think or search my way to an answer, I put myself in your able hands...

I'd like to combine data from table A (high quality data) with data from table B (fresh data) such that the entries from B are only included when the date stamp are later than those available from table A.

Both tables include entries from multiple entities, and the latest date stamp varies with those entities.

On the 4th of january, the tables may look something like:

A____________________________ B_____________________________
entity date type value entity date type value
X 1.jan 1 1 X 1.jan 1 2
X 1.jan 0 1 X 1.jan 0 2
X 2.jan 1 1 X 2.jan 1 2
Y 1.jan 1 1 (new entry)X 3.jan 1 1
Y 3.jan 1 1 Y 1.jan 1 2
Y 3.jan 1 2
(new entry)Y 4.jan 1 1


I have made an attempt at some code that I hope clarify my need:

WITH
AA AS
(SELECT entity, date, SUM(value)
FROM table_A
GROUP BY
entity,
date),

BB AS
(SELECT entity, date, SUM(value)
FROM table_B
WHERE date > ALL (SELECT date FROM AA)
GROUP BY
entity,
date
)

SELECT * FROM (SELECT * FROM AA UNION ALL SELECT * FROM BB)


Now, if the
WHERE date > ALL (SELECT date FROM AA)
would work seperately for each entity, I think have what I need.

That is, for each entity I want all entries from A, and only newer entries from B.

As the data in table A often differ from that of B (values are often corrected) I dont think I can use something like:
table A UNION ALL (table B MINUS table A)
?

Thanks

Answer

I find your question confusing, because I don't know where the aggregation is coming from.

The basic idea on getting newer rows from table_b uses conditions in the where clause, something like this:

select . . .
from table_a a
union all
select . . .
from table_b b
where b.date > (select max(a.date) from a where a.entity = b.entity);

You can, of course, run this on your CTEs, if those are what you really want to combine.