| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: how to generate the output w/o using temp table
There is a major problem with the request that you have outlined. Based upon the data and the request, it is fairly easy to deliver
the data when the vacation is in a single month or spans no more than 2 months. When the person is on vacation during a month that
is not the start_date nor the end_date, the row will not show up. In your example, #3 would show up in 12/2004 and 2/2005, but not
1/2005.
Somehow, you need to iterate through a list of months between the start and end dates and find all records that fall within that range. If you have a calendar type table, you can use that. If not, you have to get creative. I've included some code below (part stolen from other posters, part from my own twisted mind). *IF* the number of months requested is greater that the number of rows in the vacation table, it will *not* work. A better solution would be to code it in PL/SQL or another language.
Regards,
Daniel Fink
SQL> create table vac_test
2 (emp_ID number,
3 startdate date,
4 enddate date);
Table created.
SQL>
SQL> insert into vac_test values ( 1, to_date('10/01/2004', 'mm/dd/yyyy'), to_date('10/18/2004', 'mm/dd/yyyy'));
1 row created.
SQL> insert into vac_test values ( 2, to_date('12/01/2004', 'mm/dd/yyyy'), to_date('12/09/2004', 'mm/dd/yyyy'));
1 row created.
SQL> insert into vac_test values ( 3, to_date('12/24/2004', 'mm/dd/yyyy'), to_date('01/02/2005', 'mm/dd/yyyy'));
1 row created.
SQL> insert into vac_test values ( 4, to_date('11/20/2004', 'mm/dd/yyyy'), to_date('12/20/2004', 'mm/dd/yyyy'));
1 row created.
SQL> insert into vac_test values ( 5, to_date('12/01/2004', 'mm/dd/yyyy'), to_date('12/04/2004', 'mm/dd/yyyy'));
1 row created.
SQL> insert into vac_test values ( 1, to_date('12/24/2004', 'mm/dd/yyyy'), to_date('12/27/2004', 'mm/dd/yyyy'));
1 row created.
SQL> insert into vac_test values ( 6, to_date('12/31/2004', 'mm/dd/yyyy'), to_date('02/28/2005', 'mm/dd/yyyy'));
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from vac_test
2 /
EMP_ID STARTDATE ENDDATE
---------- --------- ---------
1 01-OCT-04 18-OCT-04
2 01-DEC-04 09-DEC-04
3 24-DEC-04 02-JAN-05
4 20-NOV-04 20-DEC-04
5 01-DEC-04 04-DEC-04
1 24-DEC-04 27-DEC-04
6 31-DEC-04 28-FEB-05
7 rows selected.
1 select iv.vacation_month,
2 vt.emp_id,
3 vt.startdate,
4 vt.enddate
5 from (select to_char(add_months(to_date('&&start_mmyyyy', 'MM/YYYY'),(rownum - 1)), 'YYYY/MM') vacation_month
6 from vac_test
7 where rownum - 1 <= months_between(to_date('&&end_mmyyyy', 'MM/YYYY'), to_date('&&start_mmyyyy', 'MM/YYYY'))) iv,
8 vac_test vt
old 5: from (select to_char(add_months(to_date('&&start_mmyyyy', 'MM/YYYY'),(rownum - 1)), 'YYYY/MM') vacation_month
new 5: from (select to_char(add_months(to_date('12/2004', 'MM/YYYY'),(rownum - 1)), 'YYYY/MM') vacation_month
old 7: where rownum - 1 <= months_between(to_date('&&end_mmyyyy', 'MM/YYYY'), to_date('&&start_mmyyyy', 'MM/YYYY'))) iv,
new 7: where rownum - 1 <= months_between(to_date('02/2005', 'MM/YYYY'), to_date('12/2004', 'MM/YYYY'))) iv,
VACATIO EMP_ID STARTDATE ENDDATE
------- ---------- --------- ---------
2004/12 1 24-DEC-04 27-DEC-04
2004/12 2 01-DEC-04 09-DEC-04
2004/12 3 24-DEC-04 02-JAN-05
2004/12 4 20-NOV-04 20-DEC-04
2004/12 5 01-DEC-04 04-DEC-04
2004/12 6 31-DEC-04 28-FEB-05
2005/01 3 24-DEC-04 02-JAN-05
2005/01 6 31-DEC-04 28-FEB-05
2005/02 6 31-DEC-04 28-FEB-05
9 rows selected.
SQL> undefine start_mmyyyy SQL> undefine end_mmyyyy SQL> /
old 7: where rownum - 1 <= months_between(to_date('&&end_mmyyyy', 'MM/YYYY'), to_date('&&start_mmyyyy', 'MM/YYYY'))) iv,
new 7: where rownum - 1 <= months_between(to_date('12/2004', 'MM/YYYY'), to_date('12/2004', 'MM/YYYY'))) iv,
VACATIO EMP_ID STARTDATE ENDDATE
------- ---------- --------- ---------
2004/12 1 24-DEC-04 27-DEC-04
2004/12 2 01-DEC-04 09-DEC-04
2004/12 3 24-DEC-04 02-JAN-05
2004/12 4 20-NOV-04 20-DEC-04
2004/12 5 01-DEC-04 04-DEC-04
2004/12 6 31-DEC-04 28-FEB-05
6 rows selected.
SQL>
 |
 |