10 Replies Latest reply: Sep 17, 2013 10:45 AM by ranit B RSS

    Reg: Grouping data -

    ranit B

      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

          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

            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

              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

                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
                  • 6. Re: Reg: Grouping data -
                    BluShadow

                    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

                      Thanks a lot, Pleiadian.

                       

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

                      -- Ranit

                      • 8. Re: Reg: Grouping data -
                        ranit B

                        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

                          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

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