By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
alter session set nls_date_format = 'MM/DD/YYYY HH24:MI:SS'
CREATE TABLE TABLE1(ID CHAR(10) NOT NULL
,TABLE1_VER NUMBER NOT NULL
,AUDIT_DATETIME DATE
);
CREATE TABLE TABLE2(ID CHAR(10) NOT NULL
,TABLE2_VER NUMBER NOT NULL
,AUDIT_DATETIME DATE
,ASSOCIATED_VERSION_TABLE1 CHAR(10) NOT NULL
);
Insert into TABLE1 (ID,TABLE1_VER,AUDIT_DATETIME) values ('0002073263',1,to_date('10/30/2023 09:57:05','MM/DD/YYYY HH24:MI:SS'))
1 rows affected
Insert into TABLE1 (ID,TABLE1_VER,AUDIT_DATETIME) values ('0002073263',2,to_date('10/30/2023 09:57:06','MM/DD/YYYY HH24:MI:SS'))
1 rows affected
Insert into TABLE1 (ID,TABLE1_VER,AUDIT_DATETIME) values ('0002073263',3,to_date('10/30/2023 09:57:34','MM/DD/YYYY HH24:MI:SS'))
1 rows affected
Insert into TABLE2 (ID,TABLE2_VER,AUDIT_DATETIME,ASSOCIATED_VERSION_TABLE1) values ('0002073263',1,to_date('10/30/2023 09:57:05','MM/DD/YYYY HH24:MI:SS'),1)
1 rows affected
Insert into TABLE2 (ID,TABLE2_VER,AUDIT_DATETIME,ASSOCIATED_VERSION_TABLE1) values ('0002073263',2,to_date('10/30/2023 10:55:04','MM/DD/YYYY HH24:MI:SS'),3)
1 rows affected
Insert into TABLE2 (ID,TABLE2_VER,AUDIT_DATETIME,ASSOCIATED_VERSION_TABLE1) values ('0002073263',3,to_date('10/30/2023 10:55:22','MM/DD/YYYY HH24:MI:SS'),3)
1 rows affected
SELECT COALESCE(t1.ID, t2.ID) AS ID
, t1.TABLE1_VER
, t1.AUDIT_DATETIME AS TABLE1_AUDITDATETIME
, t1.NEXT_AUDIT_DATETIME AS TABLE1_NEXT_AUDIT_DATETIME
, t2.TABLE2_VER
, t2.AUDIT_DATETIME AS TABLE2_AUDITDATETIME
, t2.NEXT_AUDIT_DATETIME AS TABLE2_NEXT_AUDIT_DATETIME
, t1.TABLE1_VER + t2.TABLE2_VER AS COMBINED_VER
FROM (
SELECT t1a.ID
, t1a.TABLE1_VER
, t1a.AUDIT_DATETIME
, COALESCE(t1b.AUDIT_DATETIME, to_date('12/31/2999 23:59:59','MM/DD/YYYY HH24:MI:SS')) AS NEXT_AUDIT_DATETIME
FROM TABLE1 t1a
LEFT JOIN TABLE1 t1b
ON t1a.ID = t1b.ID
AND t1a.TABLE1_VER + 1= t1b.TABLE1_VER
)t1
INNER JOIN (
SELECT t2a.ID
, t2a.TABLE2_VER
, t2a.ASSOCIATED_VERSION_TABLE1
, t2a.AUDIT_DATETIME
, COALESCE(t2b.AUDIT_DATETIME, to_date('12/31/2999 23:59:59','MM/DD/YYYY HH24:MI:SS')) AS NEXT_AUDIT_DATETIME
FROM TABLE2 t2a
LEFT JOIN TABLE2 t2b
ON t2a.ID = t2b.ID
AND t2a.TABLE2_VER + 1= t2b.TABLE2_VER
) t2
ON t1.ID = t2.ID
AND (
(t1.AUDIT_DATETIME BETWEEN t2.AUDIT_DATETIME AND t2.NEXT_AUDIT_DATETIME)
OR
(t2.AUDIT_DATETIME BETWEEN t1.AUDIT_DATETIME AND t1.NEXT_AUDIT_DATETIME)
);
| ID | TABLE1_VER | TABLE1_AUDITDATETIME | TABLE1_NEXT_AUDIT_DATETIME | TABLE2_VER | TABLE2_AUDITDATETIME | TABLE2_NEXT_AUDIT_DATETIME | COMBINED_VER |
|---|---|---|---|---|---|---|---|
| 0002073263 | 1 | 10/30/2023 09:57:05 | 10/30/2023 09:57:06 | 1 | 10/30/2023 09:57:05 | 10/30/2023 10:55:04 | 2 |
| 0002073263 | 2 | 10/30/2023 09:57:06 | 10/30/2023 09:57:34 | 1 | 10/30/2023 09:57:05 | 10/30/2023 10:55:04 | 3 |
| 0002073263 | 3 | 10/30/2023 09:57:34 | 12/31/2999 23:59:59 | 1 | 10/30/2023 09:57:05 | 10/30/2023 10:55:04 | 4 |
| 0002073263 | 3 | 10/30/2023 09:57:34 | 12/31/2999 23:59:59 | 2 | 10/30/2023 10:55:04 | 10/30/2023 10:55:22 | 5 |
| 0002073263 | 3 | 10/30/2023 09:57:34 | 12/31/2999 23:59:59 | 3 | 10/30/2023 10:55:22 | 12/31/2999 23:59:59 | 6 |