10 Replies Latest reply: Sep 17, 2013 5: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.

                • 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.