will all users be able to access the all objects. my user wants to query for certain range of month who are on-vacation? example: user enter starting date 10/2004 ending date: 02/2005 result: mm/yyyy id 10/2004 1 11/2004 1 12/2004 1 2 3 01/2005 2 3 02/2005 3 Charudatta Joshi writes: > > Hi, > > See if the following solution comes close: > > Regards, > Charu. > > create table tmp > (id number, > startdate date, > enddate date); > > insert into tmp values ( > 1, > to_date('10/01/2004', 'mm/dd/yyyy'), > to_date('12/13/2004', 'mm/dd/yyyy')); > > insert into tmp values ( > 2, > to_date('12/01/2004', 'mm/dd/yyyy'), > to_date('12/02/2005', 'mm/dd/yyyy')); > > insert into tmp values ( > 3, > to_date('12/03/2004', 'mm/dd/yyyy'), > to_date('02/20/2005', 'mm/dd/yyyy')); > > Query: > > > select id, > TO_CHAR(mnth, 'MM/YYYY') > from tmp a, > (select trunc(add_months(sysdate, - rownum + 1), 'MM') mnth > from all_objects > union all > select trunc(add_months(sysdate, rownum), 'MM') mnth > from all_objects) b > where b.mnth > a.startdate and a.mnth <= enddate > ---------------------------------------------------------------- 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 -----------------------------------------------------------------