Re: Single Query for getting Range Values
Date: Mon, 5 Jul 2010 16:14:54 +0100
Message-ID: <AANLkTimiy19c_QQ_Ag_We4BvkQXeB3MqWY_cEl0OENYX_at_mail.gmail.com>
Hi,
I had been trying to do this using analytics, but I could only get each column of dates using seperate queries and could not figure out how to join them, the left outer join to get the start date is inspired.
Pete
On Mon, Jul 5, 2010 at 12:48 PM, Stephane Faroult <sfaroult_at_roughsea.com>wrote:
> 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
> > > >
> *On Mon 5/07/10 08:54 , Sreejith S Nair Sreejith.Sreekantan_at_ibsplc.comsent:
> *
>
> 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."
> > > > >
-- Regards Pete -- http://www.freelists.org/webpage/oracle-lReceived on Mon Jul 05 2010 - 10:14:54 CDT