I have a table in Oracle:
x
select * from leave;
--LEAVE_DTL_ID is auto incremented
LEAVE_DTL_ID STAFF_ID START_DT END_DT
------------ ---------- --------- ---------
49 654 21-JAN-19 23-JAN-19
186 55 02-MAY-19 06-MAY-19
280 354 09-JUN-19 14-JUN-19
I want to combine the START_DT
and END_DT
columns to a single column named ‘DATES’ as per the corresponding date range.
Eg: take LEAVE_DTL_ID = 186
it has START_DT = '02-MAY-19'
and END_DT = '06-MAY-19'
.
I want to populate the data as
LEAVE_DTL_ID STAFF_ID DATES
------------ ---------- ---------
186 55 02-MAY-19
187 55 03-MAY-19
188 55 04-MAY-19
189 55 05-MAY-19
190 55 06-MAY-19
Is there any way to accomplish this ?
Advertisement
Answer
SQL> with leave (leave_dtl_id, staff_id, start_dt, end_dt) as
2 (select 49, 654, date '2019-01-21', date '2019-01-23' from dual union all
3 select 186, 55, date '2019-05-02', date '2019-05-06' from dual
4 )
5 select
6 (leave_dtl_id + column_value - 1) as leave_dtl_id,
7 staff_id,
8 (start_dt + column_value - 1) as dates
9 from leave cross join
10 table(cast(multiset(select level from dual
11 connect by level <= end_dt - start_dt + 1
12 ) as sys.odcinumberlist))
13 order by staff_id, dates;
LEAVE_DTL_ID STAFF_ID DATES
------------ ---------- --------
186 55 02.05.19
187 55 03.05.19
188 55 04.05.19
189 55 05.05.19
190 55 06.05.19
49 654 21.01.19
50 654 22.01.19
51 654 23.01.19
8 rows selected.
SQL>