Re: how to generate the output w/o using temp table
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 02 Aug 2004 11:01:11 -0600
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
9 where iv.vacation_month between to_char(startdate , 'YYYY/MM') and
to_char(enddate, 'YYYY/MM')
10* order by to_date(iv.vacation_month, 'YYYY/MM'), emp_id
SQL> /
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> /
Enter value for start_mmyyyy: 12/2004
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
Enter value for end_mmyyyy: 12/2004
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>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
