Re: Single Query for getting Range Values

 

        select acy_cd,
        min(start_dt) start_dt,
        max(end_dte) end_dte
 from (select level lvl,
               t.*,
               substr(sys_connect_by_path(to_char(t.start_dt,
'DDMONYYYY'), ','),
                      2, 9) path_root
       from test_acy t
       connect by acy_cd = prior acy_cd
          and start_dt = prior end_dte
       start with t.start_dt in (select t2.start_dt
                                 from test_acy t2
                                      left outer join test_acy t3
                                             on t2.start_dt =
t3.end_dte
                                            and t2.acy_cd = t3.acy_cd
                                 where t3.end_dte is null))
 group by acy_cd, path_root
 / 
        HTH 
        S Faroult
  BODY { font-family:Arial, Helvetica, sans-serif;font-size:12px; } 
 On Mon 5/07/10 08:54 , Sreejith S Nair
Sreejith.Sreekantan@xxxxxxxxxx sent:
  Hi list members, 
 Is there a way - a single query which I can use to fetch data from
the below table, in this format 
 Expected Output format 
 AB        01Jan2010        04Jan2010 
 AB        06Jan2010        07Jan2010 
 AB        09Jan2010        11Jan2010 
 CD        11Jan2010        12Jan2010. 
 createtabletest_acy(acy_cd varchar2(5),start_dt date,end_dte date); 
 insertintotest_acy values('AB','01Jan2010','02Jan2010'); 
 insertintotest_acy values('AB','02Jan2010','03Jan2010'); 
 insertintotest_acy values('AB','03Jan2010','04Jan2010'); 
 insertintotest_acy values('AB','06Jan2010','07Jan2010'); 
 insertintotest_acy values('AB','09Jan2010','10Jan2010'); 
 insertintotest_acy values('AB','10Jan2010','11Jan2010'); 
 insertintotest_acy values('CD','11Jan2010','12Jan2010'); 
 Expected Output format 
 AB        01Jan2010        04Jan2010 
 AB        06Jan2010        07Jan2010 
 AB        09Jan2010        11Jan2010 
 CD        11Jan2010        12Jan2010. 
 I may need the row as a block,which separates on a discontinuous
date range.ie I am having AB block from 01Jan to 04Jan, again a block
from 06Jan to 07Jan,like that. 
 MANY THANKS, 
 SREEJITH  NAIR 

----------------------------------------------------------------------------------------------------------------------------------------------------------------

 DISCLAIMER:
 "The information in this e-mail and any attachment is intended only
for the person to whom it is addressed and may contain confidential
and/or privileged material. If you have received this e-mail in
error, kindly contact the sender and destroy all copies of the
original communication. IBS makes no warranty, express or implied,
nor guarantees the accuracy, adequacy or completeness of the
information contained in this email or any attachment and is not
liable for any errors, defects, omissions, viruses or for resultant
loss or damage, if any, direct or indirect."

Other related posts: