Expand Range of dates in rows - Oracle SQL-CodePudding

I have a record that shows an employee has taken an Annual Leave from 23/03/2021 - 26/03/2021 i.e. 3 days as shown below.

I am trying to split the record in three start dates and end dates to get individual records. For instance:

The main reason for doing is to sum total leave taken by the organisation in one single month.

Is this possible? Tried a few different things but no luck.

CodePudding user response：

Here's one option; read comments within code.

SQL> with
  2  -- sample data
  3  data (id, start_date, end_date, att_type, descr, abs_days, abs_hours) as
  4    (select 1, date '2021-04-23', date '2021-04-26', 3000, 'ANN', 3, 29 from dual),
  5  -- calculate number of days when person identified by ID has been absent
  6  temp (id, diff) as
  7    (select id, end_date - start_date
  8     from data
  9    )
 10  -- hierarchical query, to create as many rows as there are days. Cross join is here
 11  -- to avoid duplicates when you run that query for more than a single person
 12  select
 13    d.id,
 14    d.start_date   column_value - 1 start_date,
 15    d.start_date   column_value     end_date,
 16    d.att_type,
 17    d.descr,
 18    d.abs_days / t.diff abs_days,
 19    round(d.abs_hours / t.diff, 2) abs_hours
 20  from data d join temp t on t.id = d.id
 21  cross join table(cast(multiset(select level from dual
 22                                 connect by level <= t.diff
 23                                ) as sys.odcinumberlist))
 24  order by d.id, d.start_date;

        ID START_DATE END_DATE     ATT_TYPE DES   ABS_DAYS  ABS_HOURS
---------- ---------- ---------- ---------- --- ---------- ----------
         1 23/04/2021 24/04/2021       3000 ANN          1       9,67
         1 24/04/2021 25/04/2021       3000 ANN          1       9,67
         1 25/04/2021 26/04/2021       3000 ANN          1       9,67

SQL>

From my point of view, ABS_HOURS you posted in sample data is wrong. Working day has as many hours as it has (here, where I live, it is 8 hours per day) which makes exactly 24 hours per 3 days. If it is different where you live, no problem - could be 9 hours or 10 hours, but that number is (or, at least, should be) the same every day. Therefore, 29 hours per 3 days looks wrong to me.

That reflects ABS_HOURS in the final query. You wanted 9.6 9.6 9.7 to make 29 hours. That's not easy (at least, not for me) so I'd switch to PL/SQL in that case.