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

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.

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

CodePudding user response：

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.

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.

CodePudding user response：

You can get this using connect by level:

select tbl.*
    , StartDate   level - 1 "Date"
from tbl
connect by level < (EndDate - StartDate)   2
    and Entity = prior Entity
    and prior dbms_random.value() is not null