SQL & PL/SQL

Hello

I am struggling with joining 2 tables based on a date join, where the query should be reporting back the amounts based on the day of execution.

So here is the issue and all of these are just examples)

The primary key of this table above for Agreements is the AGREEMENTID + EFFV_STRT_DT

Very simple table, only 1 active row depending on what day the end user is wondering about. Valid to and from dates illustrate that.

Here is another table)

The primary key of this table is AGREEMENTID + CNPARTYID + EFFV_STRT_DT.

Very simple table, only 1 active row with the combination of agreement + counter party during a certain time, which the Valid to and From dates illustrate date.(EFFV_STRT / EFFV_END)

Now, we want to join these two tables.

The first join condition is very simple

AGRM.AGREEMENT_ID = AMT_GUAR.AGREEMENT_ID

Now we want to join the dates.

I've tried various methods but I keep missing out rows depending on the date I run the query on.

For instance, Iv tried
1. WHERE AGRM.EFFV_STRT_DT between AMT_GUAR.EFFV_STRT_DT and EFFV_END_DT.

and also

2. AGRM.EFFV_STRT_DT <= AMT_GUAR.EFFV_END_DT AND AGRM.EFFV_STRT_DT >= AMT_GUAR.EFFV_STRT_DT

But that gives me always a gap of data, and here is why.

select * from AGREEMENT t1,

AGREEMENT_GUAR t2

where t1.agreement_id = t2.agreement_id

and (t1.effv_strt_dt <= t2.effv_end_dt and t1.effv_strt_dt>= t2.effv_strt_dt)

--Here below I'm adding the date filter which I'm running the report on, so the 24th of january.

AND nvl(TO_CHAR ( t1.effv_strt_dt, 'YYYY-MM-DD HH24:MI:SS'),to_date('99991231','YYYYMMDD')) <=  '2018-01-24 23:59:59'

AND TO_CHAR ( NVL (t1.effv_end_dt, SYSDATE + 1), 'YYYY-MM-DD HH24:MI:SS') >=       '2018-01-24 23:59:59';

This query gives me this result from the AMT_GUAR table for these 2 rows)

It gives me the values 5 and 15 from the AMT_GUAR table, but as you see the 1st row with the amount of 5 is only valid from 22nd to 23rd, not valid on the 24th what I was asking for.

The reason for this is the row that is valid on the 24th from the agrm table is this one, and the dates for that one join up to get the 1st from the table above.

Instead I wanted these rows)

Its a bit confusing I know but I hope I made my point clear.

Any ideas on how to solve this ?

Please note before responding)

* We cannot add another date filter similar as to one written above  on the 2nd table AMT_GUAR  - that's obvious else I would not  post this.

* The question is directly related to SQL joins , not anything else, materialized views or functions or anything of that sort.

* Please do not respond in a way that is not helpful, like  "You need to remodel your data model or upgrade to Oracle 12c".

* All unbeneficial answers will be removed

Thanks