Common Table Expression to Replicate Dates

Question

I may be overthinking this, but here goes:

I have 2 tables, one which is a date table; say "Dates", which holds all the dates in a calendar year. It looks like this:

Date
01-Jan-22
02-Jan-22
03-Jan-22
04-Jan-22
04-Jan-22
05-Jan-22

I have another table which is a "Deals" table and looks like this:

DealNo.       StartDate         EndDate           Amount
1ahk          02-Jan-22         04-Jan-22         10,000
1hyt          03-Jan-22         05-Jan-22          5,000
5tiu          01-Jan-22         03-Jan-22          8,000

I would like to join these 2 and produce a table that looks like the following:

Date            DealNo.       Amount
02-Jan-22       1ahk          10,000
03-Jan-22       1ahk          10,000
04-Jan-22       1ahk          10,000
03-Jan-22       1hyt           5,000
04-Jan-22       1hyt           5,000
05-Jan-22       1hyt           5,000
01-Jan-22       5tiu           8,000
02-Jan-22       5tiu           8,000
03-Jan-22       5tiu           8,000

Any assistance on how I can achieve this with Oracle SQL, will be greatly appreciated.

Hope I have explained well.

HeC

Littlefoot · Answer

That's just a simple join which uses the between operator.

Sample data:

SQL> with dates (datum) as
  2    (select date '2022-01-01' from dual union all
  3     select date '2022-01-02' from dual union all
  4     select date '2022-01-03' from dual union all
  5     select date '2022-01-04' from dual union all
  6     select date '2022-01-05' from dual union all
  7     select date '2022-01-06' from dual
  8    ),
  9  deals (deal_no, start_date, end_date, amount) as
 10    (select '1ahk', date '2022-01-02', date '2022-01-04', 10000 from dual union all
 11     select '1hyt', date '2022-01-03', date '2022-01-05',  5000 from dual union all
 12     select '5tiu', date '2022-01-01', date '2022-01-03',  8000 from dual
 13    )

Query begins here:

 14  select a.datum, b.deal_no, b.amount
 15  from dates a join deals b on a.datum between b.start_date and b.end_date
 16  order by b.deal_no, a.datum
 17  /

DATUM     DEAL     AMOUNT
--------- ---- ----------
02-Jan-22 1ahk      10000
03-Jan-22 1ahk      10000
04-Jan-22 1ahk      10000
03-Jan-22 1hyt       5000
04-Jan-22 1hyt       5000
05-Jan-22 1hyt       5000
01-Jan-22 5tiu       8000
02-Jan-22 5tiu       8000
03-Jan-22 5tiu       8000

9 rows selected.

SQL>

Common Table Expression to Replicate Dates

1 Answers