Hello .
I need you help please to generate dates between 2 columns date
i have a table "empsts_attan" in Database
| EMPID | start_date | end_dates
|
|---|
| 1 | 01/01/2020 | 05/01/2020 |
| 1 | 01/02/2020 | 01/02/2020 |
| 2 | 03/02/2020 | null |
| 3 | 04/02/2020 | 05/02/2020 |
i want to get query like this :
| EMPID | Date |
|---|
| 1 | 01/01/2020 |
| 1 | 02/01/2020 |
| 1 | 03/01/2020 |
| 1 | 04/01/2020 |
| 1 | 05/01/2020 |
| 1 | 01/02/2020 |
| 2 | 03/02/2020 |
| 3 | 04/02/2020 |
| 3 | 05/02/2020 |
I'm using Oracle database 12 c 12.2.0
Table script :
CREATE TABLE EMPSTS_ATTAN
(
EMPID NUMBER,
START_DATE DATE,
END_DATES DATE
)
insert query script :
SET DEFINE OFF;
Insert into EMPSTS_ATTAN
(EMPID, START_DATE, END_DATES)
Values
(1, TO_DATE('01/01/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/05/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EMPSTS_ATTAN
(EMPID, START_DATE, END_DATES)
Values
(1, TO_DATE('02/01/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('02/01/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EMPSTS_ATTAN
(EMPID, START_DATE)
Values
(2, TO_DATE('02/03/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
Insert into EMPSTS_ATTAN
(EMPID, START_DATE, END_DATES)
Values
(3, TO_DATE('01/04/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('02/05/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;
Thanks in advance