Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0
Two tables A and B. Each has gapless date intervalls.
dat_from is not null, the last dat_to can be null
The intervalls don't necessarily start/end at the same date
Each intervall has an amount
I need intervalls that have the max of each
Data for each month
Dez Jan Feb Mar Apr May Jun Jun Aug Sep Oct Nov Dec Jan
A 1 - 1 - 1 2 - 2 - 2 1 - 1 - 1 0 - 0 - 0 1 - 1
B x 0 - 0 - 0 2 - 2 - 2 1 0 - 0 - 0 - 0 - 0 x
Max 1 - 1 - 1 2 - 2 - 2 - 2 1 - 1 0 - 0 - 0 - 0 1
Expected output similar to
2023/12 - 2024/02 1
2024/03 - 2024/06 2
2024/07 - 2024/08 1
2024/09 - 2024/11 0
2024/12 - 1
WITH tab_a AS (
SELECT DATE '2023-12-01' dat_from, DATE '2024-02-29' dat_to, 1 amount FROM dual UNION ALL
SELECT DATE '2024-03-01' dat_from, DATE '2024-05-31' dat_to, 2 amount FROM dual UNION ALL
SELECT DATE '2024-06-01' dat_from, DATE '2024-08-31' dat_to, 1 amount FROM dual UNION ALL
SELECT DATE '2024-09-01' dat_from, DATE '2024-11-30' dat_to, 0 amount FROM dual UNION ALL
SELECT DATE '2024-12-01' dat_from, NULL dat_to, 1 amount FROM dual
)
,tab_b AS (
SELECT DATE '2024-01-01' dat_from, DATE '2024-03-31' dat_to, 0 amount FROM dual UNION ALL
SELECT DATE '2024-04-01' dat_from, DATE '2024-06-30' dat_to, 2 amount FROM dual UNION ALL
SELECT DATE '2024-07-01' dat_from, DATE '2024-07-31' dat_to, 1 amount FROM dual UNION ALL
SELECT DATE '2024-08-01' dat_from, DATE '2024-12-31' dat_to, 0 amount FROM dual
)
SELECT ???
Best Regards
Marcus