FAQ
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."

Search Discussions

  • Peter Hitchman at Jul 5, 2010 at 3:14 pm
    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 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-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJul 5, '10 at 6:54a
activeJul 5, '10 at 3:14p
posts2
users2
websiteoracle.com

2 users in discussion

Peter Hitchman: 1 post Sreejith S Nair: 1 post

People

Translate

site design / logo © 2022 Grokbase