Single Query for getting Range Values

From: Sreejith S Nair <Sreejith.Sreekantan_at_ibsplc.com>
Date: Mon, 5 Jul 2010 12:24:09 +0530
Message-ID: <OF30C44B9C.24DC9C69-ON65257757.0025717D-65257757.0025DEE5_at_ibsplc.com>



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.


create table test_acy(acy_cd varchar2(5),start_dt date,end_dte date);

insert into test_acy values('AB','01Jan2010','02Jan2010');
insert into test_acy values('AB','02Jan2010','03Jan2010');
insert into test_acy values('AB','03Jan2010','04Jan2010');
insert into test_acy values('AB','06Jan2010','07Jan2010');
insert into test_acy values('AB','09Jan2010','10Jan2010');
insert into test_acy values('AB','10Jan2010','11Jan2010');

insert into test_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."



--
http://www.freelists.org/webpage/oracle-l


picture
Received on Mon Jul 05 2010 - 01:54:09 CDT

Original text of this message