Repeating value for all dates that fall between two columns

Question

I am looking to get the daily rate of individuals given a time range in two columns.

Start Date	End Date	Daily Rate	Entity
07/23	08/06	212	A
07/23	08/06	175	B
07/23	08/06	202	C
07/23	08/06	301	D
07/23	08/06	215	E

For all dates in this range I want a row that will repeat each entity's daily rate. Like below but this trend will continue on entity A until it hits 08/06 and then move onto entity B.

Start Date	End Date	Daily Rate	Entity	Date
07/23	08/06	212	A	07/23
07/23	08/06	212	A	07/24
07/23	08/06	212	A	07/25
07/23	08/06	212	A	07/26
07/23	08/06	212	A	07/27
...	...	...	...	...
07/23	08/06	212	A	08/06
07/23	08/06	175	B	07/23
07/23	08/06	175	B	07/24
...	...	...	...	...

I also need this to work for any date range, not just this one date range.

Does this answer your question? [Generating dates between two dates](https://stackoverflow.com/questions/16207543/generating-dates-between-two-dates) — astentx, Sep 03 '22 at 06:16

score 1 · Answer 1 · answered Sep 02 '22 at 18:25

You can use recursive subquery factoring:

with rcte (start_date, end_date, daily_rate, entity, one_date) as (
  select start_date, end_date, daily_rate, entity, start_date
  from your_table
  union all
  select start_date, end_date, daily_rate, entity, one_date + 1
  from rcte
  where one_date < end_date
)
select start_date, end_date, daily_rate, entity, one_date
from rcte
order by entity, one_date

The anchor member gets the original table data plus a starting date value equal to the start date. The recursive member then adds one day to that date, until it reaches the end date.

START_DATE	END_DATE	DAILY_RATE	ENTITY	ONE_DATE
2022-07-23	2022-08-06	212	A	2022-07-23
2022-07-23	2022-08-06	212	A	2022-07-24
2022-07-23	2022-08-06	212	A	2022-07-25
2022-07-23	2022-08-06	212	A	2022-07-26
2022-07-23	2022-08-06	212	A	2022-07-27
...	...	...	...	...
2022-07-23	2022-08-06	212	A	2022-08-03
2022-07-23	2022-08-06	212	A	2022-08-04
2022-07-23	2022-08-06	212	A	2022-08-05
2022-07-23	2022-08-06	212	A	2022-08-06
2022-07-23	2022-08-06	175	B	2022-07-23
2022-07-23	2022-08-06	175	B	2022-07-24
...	...	...	...	...
2022-07-23	2022-08-06	175	B	2022-08-06
2022-07-23	2022-08-06	202	C	2022-07-23
2022-07-23	2022-08-06	202	C	2022-07-24
...	...	...	...	...
2022-07-23	2022-08-06	202	C	2022-08-06
2022-07-23	2022-08-06	301	D	2022-07-23
2022-07-23	2022-08-06	301	D	2022-07-24
...	...	...	...	...
2022-07-23	2022-08-06	301	D	2022-08-06
2022-07-23	2022-08-06	215	E	2022-07-23
2022-07-23	2022-08-06	215	E	2022-07-24
...	...	...	...	...
2022-07-23	2022-08-06	215	E	2022-08-06

db<>fiddle

You could also use a hierarchical query, but that gets a bit messy (or at least unintuitive) when dealing with multiple source rows as you have to introduce an indeterminate function call to make it work.

I think this is on the right track unfortunately I cant use cte. I am wondering if you you would be able to help with the hierarchical query or another method — FinPM, Sep 02 '22 at 19:31
The answer by SadlyFullStack shows a hierarchical query. But why cat'y you use a CTE? (Recursive wasn't available before 11gR2 I think, but that's pretty old...) — Alex Poole, Sep 02 '22 at 19:38
I believe it is this same issue. https://stackoverflow.com/questions/62223226/netsuite-odbc-limitations — FinPM, Sep 02 '22 at 19:44

MT0 · Answer 2 · 2022-09-02T20:42:03.720

From Oracle 12, you can CROSS APPLY a hierarchical query:

SELECT *
FROM   your_table t
       CROSS APPLY (
         SELECT start_date + LEVEL - 1 AS one_date
         FROM   DUAL
         CONNECT BY
                start_date + LEVEL - 1 <= end_date
       )

or CROSS JOIN LATERAL:

SELECT *
FROM   your_table t
       CROSS JOIN LATERAL (
         SELECT start_date + LEVEL - 1 AS one_date
         FROM   DUAL
         CONNECT BY
                start_date + LEVEL - 1 <= end_date
       )

Or, on earlier versions, you can JOIN to a hierarchical query that generates sufficient rows to cover all the ranges:

SELECT t.*,
       t.start_date + l.day AS one_date
FROM   your_table t
       INNER JOIN (
         SELECT LEVEL - 1 AS day
         FROM   (SELECT MAX(end_date - start_date) AS diff FROM your_table)
         CONNECT BY LEVEL - 1 <= diff
       ) l
       ON (l.day <= t.end_date - t.start_date)

db<>fiddle here

Repeating value for all dates that fall between two columns

2 Answers2