This discussion is archived
10 Replies Latest reply: Sep 17, 2013 3:43 AM by Pleiadian RSS

Reg: Grouping data -

ranit B Expert
Currently Being Moderated

Hi Experts,

 

I'm trying to find the proper START_DATE and END_DATE by group the rows but not able to achieve it.

Could you please give some pointers to this?

 

/*

INPUT DATA

*/

with table_x as(

select '12321' acct, '101' coll, TO_DATE('1/1/2013','mm/dd/yyyy') dt from dual union all

select '12321', '101' coll, TO_DATE('1/2/2013','mm/dd/yyyy') dt from dual union all

select '12321', '101' coll, TO_DATE('1/3/2013','mm/dd/yyyy') dt from dual union all

select '12321', '101' coll, TO_DATE('1/4/2013','mm/dd/yyyy') dt from dual union all

select '12321', '504' coll, TO_DATE('1/5/2013','mm/dd/yyyy') dt from dual union all

select '12321', '504' coll, TO_DATE('1/6/2013','mm/dd/yyyy') dt from dual union all

select '12321', '504' coll, TO_DATE('1/7/2013','mm/dd/yyyy') dt from dual union all

select '12321', '101' coll, TO_DATE('1/8/2013','mm/dd/yyyy') dt from dual union all

select '12321', '101' coll, TO_DATE('1/9/2013','mm/dd/yyyy') dt from dual union all

select '12321', '101' coll, TO_DATE('1/10/2013','mm/dd/yyyy') dt from dual union all

select '12321', '985' coll, TO_DATE('1/11/2013','mm/dd/yyyy') dt from dual union all

select '12321', '985' coll, TO_DATE('1/12/2013','mm/dd/yyyy') dt from dual union all

select '32123', '503' coll, TO_DATE('1/1/2013','mm/dd/yyyy') dt from dual union all

select '32123', '503' coll, TO_DATE('1/2/2013','mm/dd/yyyy') dt from dual union all

select '32123', '503' coll, TO_DATE('1/3/2013','mm/dd/yyyy') dt from dual union all

select '32123', '503' coll, TO_DATE('1/4/2013','mm/dd/yyyy') dt from dual union all

select '32123', '503' coll, TO_DATE('1/5/2013','mm/dd/yyyy') dt from dual union all

select '32123', '108' coll, TO_DATE('1/6/2013','mm/dd/yyyy') dt from dual union all

select '32123', '108' coll, TO_DATE('1/7/2013','mm/dd/yyyy') dt from dual union all

select '32123', '457' coll, TO_DATE('1/8/2013','mm/dd/yyyy') dt from dual union all

select '32123', '108' coll, TO_DATE('1/9/2013','mm/dd/yyyy') dt from dual union all

select '32123', '108' coll, TO_DATE('1/10/2013','mm/dd/yyyy') dt from dual union all

select '32123', '108' coll, TO_DATE('1/11/2013','mm/dd/yyyy') dt from dual union all

select '32123', '108' coll, TO_DATE('1/12/2013','mm/dd/yyyy') dt from dual

)

SELECT * FROM table_x;

 

/*

EXPECTED OUTPUT

*/

123211011-Jan-134-Jan-13
123215045-Jan-137-Jan-13
123211018-Jan-1310-Jan-13
1232198511-Jan-1312-Jan-13
321235031-Jan-135-Jan-13
321231086-Jan-137-Jan-13
321234578-Jan-138-Jan-13
321231089-Jan-1312-Jan-13

 

 

/*

My Try

*/

with table_x as(

select '12321' acct, '101' coll, TO_DATE('1/1/2013','mm/dd/yyyy') dt from dual union all

select '12321', '101' coll, TO_DATE('1/2/2013','mm/dd/yyyy') dt from dual union all

select '12321', '101' coll, TO_DATE('1/3/2013','mm/dd/yyyy') dt from dual union all

select '12321', '101' coll, TO_DATE('1/4/2013','mm/dd/yyyy') dt from dual union all

select '12321', '504' coll, TO_DATE('1/5/2013','mm/dd/yyyy') dt from dual union all

select '12321', '504' coll, TO_DATE('1/6/2013','mm/dd/yyyy') dt from dual union all

select '12321', '504' coll, TO_DATE('1/7/2013','mm/dd/yyyy') dt from dual union all

select '12321', '101' coll, TO_DATE('1/8/2013','mm/dd/yyyy') dt from dual union all

select '12321', '101' coll, TO_DATE('1/9/2013','mm/dd/yyyy') dt from dual union all

select '12321', '101' coll, TO_DATE('1/10/2013','mm/dd/yyyy') dt from dual union all

select '12321', '985' coll, TO_DATE('1/11/2013','mm/dd/yyyy') dt from dual union all

select '12321', '985' coll, TO_DATE('1/12/2013','mm/dd/yyyy') dt from dual union all

select '32123', '503' coll, TO_DATE('1/1/2013','mm/dd/yyyy') dt from dual union all

select '32123', '503' coll, TO_DATE('1/2/2013','mm/dd/yyyy') dt from dual union all

select '32123', '503' coll, TO_DATE('1/3/2013','mm/dd/yyyy') dt from dual union all

select '32123', '503' coll, TO_DATE('1/4/2013','mm/dd/yyyy') dt from dual union all

select '32123', '503' coll, TO_DATE('1/5/2013','mm/dd/yyyy') dt from dual union all

select '32123', '108' coll, TO_DATE('1/6/2013','mm/dd/yyyy') dt from dual union all

select '32123', '108' coll, TO_DATE('1/7/2013','mm/dd/yyyy') dt from dual union all

select '32123', '457' coll, TO_DATE('1/8/2013','mm/dd/yyyy') dt from dual union all

select '32123', '108' coll, TO_DATE('1/9/2013','mm/dd/yyyy') dt from dual union all

select '32123', '108' coll, TO_DATE('1/10/2013','mm/dd/yyyy') dt from dual union all

select '32123', '108' coll, TO_DATE('1/11/2013','mm/dd/yyyy') dt from dual union all

select '32123', '108' coll, TO_DATE('1/12/2013','mm/dd/yyyy') dt from dual

),

x1 AS(

  SELECT ROWNUM rn, acct, coll, dt

  FROM table_x

),

x2 AS(

  SELECT e.*,

  LAG(rn) OVER(PARTITION BY acct||coll ORDER BY rn) lg1,

  rn - 1 lg2,

  LEAD(rn) OVER(PARTITION BY acct||coll ORDER BY rn) ld1,

  rn + 1 ld2

FROM x1 e

),

x3 AS (

  SELECT e.rn, e.acct, e.coll, e.lg1, e.lg2, e.ld1, e.ld2,

    (CASE WHEN (lg1 <> lg2) OR (lg1 IS NULL)

    THEN

      dt

    END) start_date,

    (CASE WHEN (ld1 <> ld2) OR (ld1 IS NULL)

    THEN

      dt

    END) end_date

  FROM x2 e

  ORDER BY rn

)

SELECT * FROM x3;

 

But I'm not able to group the similar rows properly.

 

Help much appreciated.

Thanks,

-- Ranit

(Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)

  • 1. Re: Reg: Grouping data -
    Pleiadian Journeyer
    Currently Being Moderated

    SELECT acct,coll,min(dt) start_date, max(dt) end_date FROM table_x group by acct,coll

     

    Edit: The requirements changed after posting this.

  • 2. Re: Reg: Grouping data -
    Suri Pro
    Currently Being Moderated

    Seems to be simple

     

    with table_x as(

    select '12321' acct, '101' coll, TO_DATE('1/1/2013','mm/dd/yyyy') dt from dual union all

    select '12321', '101' coll, TO_DATE('1/2/2013','mm/dd/yyyy') dt from dual union all

    select '12321', '101' coll, TO_DATE('1/3/2013','mm/dd/yyyy') dt from dual union all

    select '12321', '101' coll, TO_DATE('1/4/2013','mm/dd/yyyy') dt from dual union all

    select '12321', '504' coll, TO_DATE('1/5/2013','mm/dd/yyyy') dt from dual union all

    select '12321', '504' coll, TO_DATE('1/6/2013','mm/dd/yyyy') dt from dual union all

    select '12321', '504' coll, TO_DATE('1/7/2013','mm/dd/yyyy') dt from dual union all

    select '12321', '101' coll, TO_DATE('1/8/2013','mm/dd/yyyy') dt from dual union all

    select '12321', '101' coll, TO_DATE('1/9/2013','mm/dd/yyyy') dt from dual union all

    select '12321', '101' coll, TO_DATE('1/10/2013','mm/dd/yyyy') dt from dual union all

    select '12321', '985' coll, TO_DATE('1/11/2013','mm/dd/yyyy') dt from dual union all

    select '12321', '985' coll, TO_DATE('1/12/2013','mm/dd/yyyy') dt from dual union all

    select '32123', '503' coll, TO_DATE('1/1/2013','mm/dd/yyyy') dt from dual union all

    select '32123', '503' coll, TO_DATE('1/2/2013','mm/dd/yyyy') dt from dual union all

    select '32123', '503' coll, TO_DATE('1/3/2013','mm/dd/yyyy') dt from dual union all

    select '32123', '503' coll, TO_DATE('1/4/2013','mm/dd/yyyy') dt from dual union all

    select '32123', '503' coll, TO_DATE('1/5/2013','mm/dd/yyyy') dt from dual union all

    select '32123', '108' coll, TO_DATE('1/6/2013','mm/dd/yyyy') dt from dual union all

    select '32123', '108' coll, TO_DATE('1/7/2013','mm/dd/yyyy') dt from dual union all

    select '32123', '457' coll, TO_DATE('1/8/2013','mm/dd/yyyy') dt from dual union all

    select '32123', '108' coll, TO_DATE('1/9/2013','mm/dd/yyyy') dt from dual union all

    select '32123', '108' coll, TO_DATE('1/10/2013','mm/dd/yyyy') dt from dual union all

    select '32123', '108' coll, TO_DATE('1/11/2013','mm/dd/yyyy') dt from dual union all

    select '32123', '108' coll, TO_DATE('1/12/2013','mm/dd/yyyy') dt from dual

    )

    SELECT t.acct,t.coll,min(t.dt),max(t.dt) FROM table_x t
    group by t.acct,t.coll

  • 3. Re: Reg: Grouping data -
    BluShadow Guru Moderator
    Currently Being Moderated

    Like this?

     

    SQL> with table_x as(
      2   select '12321' acct, '101' coll, TO_DATE('1/1/2013','mm/dd/yyyy') dt from dual union all
      3   select '12321', '101' coll, TO_DATE('1/2/2013','mm/dd/yyyy') dt from dual union all
      4   select '12321', '101' coll, TO_DATE('1/3/2013','mm/dd/yyyy') dt from dual union all
      5   select '12321', '101' coll, TO_DATE('1/4/2013','mm/dd/yyyy') dt from dual union all
      6   select '12321', '504' coll, TO_DATE('1/5/2013','mm/dd/yyyy') dt from dual union all
      7   select '12321', '504' coll, TO_DATE('1/6/2013','mm/dd/yyyy') dt from dual union all
      8   select '12321', '504' coll, TO_DATE('1/7/2013','mm/dd/yyyy') dt from dual union all
      9   select '12321', '101' coll, TO_DATE('1/8/2013','mm/dd/yyyy') dt from dual union all
    10   select '12321', '101' coll, TO_DATE('1/9/2013','mm/dd/yyyy') dt from dual union all
    11   select '12321', '101' coll, TO_DATE('1/10/2013','mm/dd/yyyy') dt from dual union all
    12   select '12321', '985' coll, TO_DATE('1/11/2013','mm/dd/yyyy') dt from dual union all
    13   select '12321', '985' coll, TO_DATE('1/12/2013','mm/dd/yyyy') dt from dual union all
    14   select '32123', '503' coll, TO_DATE('1/1/2013','mm/dd/yyyy') dt from dual union all
    15   select '32123', '503' coll, TO_DATE('1/2/2013','mm/dd/yyyy') dt from dual union all
    16   select '32123', '503' coll, TO_DATE('1/3/2013','mm/dd/yyyy') dt from dual union all
    17   select '32123', '503' coll, TO_DATE('1/4/2013','mm/dd/yyyy') dt from dual union all
    18   select '32123', '503' coll, TO_DATE('1/5/2013','mm/dd/yyyy') dt from dual union all
    19   select '32123', '108' coll, TO_DATE('1/6/2013','mm/dd/yyyy') dt from dual union all
    20   select '32123', '108' coll, TO_DATE('1/7/2013','mm/dd/yyyy') dt from dual union all
    21   select '32123', '457' coll, TO_DATE('1/8/2013','mm/dd/yyyy') dt from dual union all
    22   select '32123', '108' coll, TO_DATE('1/9/2013','mm/dd/yyyy') dt from dual union all
    23   select '32123', '108' coll, TO_DATE('1/10/2013','mm/dd/yyyy') dt from dual union all
    24   select '32123', '108' coll, TO_DATE('1/11/2013','mm/dd/yyyy') dt from dual union all
    25   select '32123', '108' coll, TO_DATE('1/12/2013','mm/dd/yyyy') dt from dual
    26  )
    27  SELECT acct, coll, min(dt) as start_date, max(dt) as end_date
    28  FROM table_x
    29  group by acct, coll
    30  order by 1,2;

     

    ACCT  COL START_DATE           END_DATE
    ----- --- -------------------- --------------------
    12321 101 01-JAN-2013 00:00:00 10-JAN-2013 00:00:00
    12321 504 05-JAN-2013 00:00:00 07-JAN-2013 00:00:00
    12321 985 11-JAN-2013 00:00:00 12-JAN-2013 00:00:00
    32123 108 06-JAN-2013 00:00:00 12-JAN-2013 00:00:00
    32123 457 08-JAN-2013 00:00:00 08-JAN-2013 00:00:00
    32123 503 01-JAN-2013 00:00:00 05-JAN-2013 00:00:00

     

    6 rows selected.

  • 4. Re: Reg: Grouping data -
    ranit B Expert
    Currently Being Moderated

    Extremely sorrry guys... I've updated the desired output. Please check again.

     

    I made a mistake previously. Apologies.

     

    NOTE: The similar groups (acct, coll) with values {12321, 101} and {32123, 108} appear twice but distantly placed.

  • 5. Re: Reg: Grouping data -
    Pleiadian Journeyer
    Currently Being Moderated
  • 6. Re: Reg: Grouping data -
    BluShadow Guru Moderator
    Currently Being Moderated

    Yes it it tabibitosan method...

     

    SQL> ed
    Wrote file afiedt.buf

      1  with table_x as(
      2   select '12321' acct, '101' coll, TO_DATE('1/1/2013','mm/dd/yyyy') dt from dual union all
      3   select '12321', '101' coll, TO_DATE('1/2/2013','mm/dd/yyyy') dt from dual union all
      4   select '12321', '101' coll, TO_DATE('1/3/2013','mm/dd/yyyy') dt from dual union all
      5   select '12321', '101' coll, TO_DATE('1/4/2013','mm/dd/yyyy') dt from dual union all
      6   select '12321', '504' coll, TO_DATE('1/5/2013','mm/dd/yyyy') dt from dual union all
      7   select '12321', '504' coll, TO_DATE('1/6/2013','mm/dd/yyyy') dt from dual union all
      8   select '12321', '504' coll, TO_DATE('1/7/2013','mm/dd/yyyy') dt from dual union all
      9   select '12321', '101' coll, TO_DATE('1/8/2013','mm/dd/yyyy') dt from dual union all
    10   select '12321', '101' coll, TO_DATE('1/9/2013','mm/dd/yyyy') dt from dual union all
    11   select '12321', '101' coll, TO_DATE('1/10/2013','mm/dd/yyyy') dt from dual union all
    12   select '12321', '985' coll, TO_DATE('1/11/2013','mm/dd/yyyy') dt from dual union all
    13   select '12321', '985' coll, TO_DATE('1/12/2013','mm/dd/yyyy') dt from dual union all
    14   select '32123', '503' coll, TO_DATE('1/1/2013','mm/dd/yyyy') dt from dual union all
    15   select '32123', '503' coll, TO_DATE('1/2/2013','mm/dd/yyyy') dt from dual union all
    16   select '32123', '503' coll, TO_DATE('1/3/2013','mm/dd/yyyy') dt from dual union all
    17   select '32123', '503' coll, TO_DATE('1/4/2013','mm/dd/yyyy') dt from dual union all
    18   select '32123', '503' coll, TO_DATE('1/5/2013','mm/dd/yyyy') dt from dual union all
    19   select '32123', '108' coll, TO_DATE('1/6/2013','mm/dd/yyyy') dt from dual union all
    20   select '32123', '108' coll, TO_DATE('1/7/2013','mm/dd/yyyy') dt from dual union all
    21   select '32123', '457' coll, TO_DATE('1/8/2013','mm/dd/yyyy') dt from dual union all
    22   select '32123', '108' coll, TO_DATE('1/9/2013','mm/dd/yyyy') dt from dual union all
    23   select '32123', '108' coll, TO_DATE('1/10/2013','mm/dd/yyyy') dt from dual union all
    24   select '32123', '108' coll, TO_DATE('1/11/2013','mm/dd/yyyy') dt from dual union all
    25   select '32123', '108' coll, TO_DATE('1/12/2013','mm/dd/yyyy') dt from dual
    26  )
    27  select acct, coll, min(dt) as start_date, max(dt) as end_date
    28  from (
    29        SELECT acct
    30              ,coll
    31              ,dt
    32              ,dt-row_number() over (order by acct, coll, dt) as grp
    33        FROM table_x
    34       )
    35  group by acct, coll, grp
    36* order by 1,3
    SQL> /

     

    ACCT  COL START_DATE           END_DATE
    ----- --- -------------------- --------------------
    12321 101 01-JAN-2013 00:00:00 04-JAN-2013 00:00:00
    12321 504 05-JAN-2013 00:00:00 07-JAN-2013 00:00:00
    12321 101 08-JAN-2013 00:00:00 10-JAN-2013 00:00:00
    12321 985 11-JAN-2013 00:00:00 12-JAN-2013 00:00:00
    32123 503 01-JAN-2013 00:00:00 05-JAN-2013 00:00:00
    32123 108 06-JAN-2013 00:00:00 07-JAN-2013 00:00:00
    32123 457 08-JAN-2013 00:00:00 08-JAN-2013 00:00:00
    32123 108 09-JAN-2013 00:00:00 12-JAN-2013 00:00:00

     

    8 rows selected.

  • 7. Re: Reg: Grouping data -
    ranit B Expert
    Currently Being Moderated

    Thanks a lot, Pleiadian.

     

    It was really something new and great learning. Much appreciated.

    -- Ranit

  • 8. Re: Reg: Grouping data -
    ranit B Expert
    Currently Being Moderated

    Thanks Blu.

    I dint know this problem has a nick name too. And as usual you were simply fabulous.

     

    Much appreciated.

     

    -- Ranit

  • 9. Re: Reg: Grouping data -
    Pleiadian Journeyer
    Currently Being Moderated

    Thanks Ranit! It is a very elegant approach... I love beautiful solutions. Coding is an Art.

     

    PS: Thanks for the points, I am now a proud owner of a silver medal

  • 10. Re: Reg: Grouping data -
    ranit B Expert
    Currently Being Moderated

    I'm sure you'll go a long way... double 'golden' medal awaits you.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points