FreeLists / oracle-l / Re: how to generate the output w/o using temp table

[Daniel Fink <Daniel.Fink@xxxxxxx>]

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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------