4 Replies Latest reply on Nov 16, 2019 7:11 PM by user13117585

    Comparing with following rows

    user13117585

      Hello everyone,

       

      I have a question and I hope somone can advise on how to proceed to have the expected results. To present the question, think about a table like this one with a few rows:

       

      CREATE TABLE operations
      (
        maintenance_id NUMBER(10), 
        operation_id NUMBER(10), 
        status VARCHAR2(10), 
        operation_date DATE
      );
      
      
      INSERT INTO operations(maintenance_id, operation_id, status, operation_date) 
      VALUES(1, 10, 'open', TO_DATE('01/01/2019', 'DD/MM/YYYY'));
      INSERT INTO operations(maintenance_id, operation_id, status, operation_date) 
      VALUES(1, 12, 'closed', TO_DATE('01/03/2019', 'DD/MM/YYYY'));
      INSERT INTO operations(maintenance_id, operation_id, status, operation_date) 
      VALUES(1, 13, 'open', TO_DATE('01/02/2019', 'DD/MM/YYYY'));
      

       

      I would like to select all the maintenance_ids that have operations with status open after a closed one and having dates also bigger. Like in this example. Operation 13 is open while 12 is closed. The date for operation 12 is bigger than in operation 13. Operation id is an id but it also gives the order.

       

      If we add this sample data:

       

      INSERT INTO operations(maintenance_id, operation_id, status, operation_date) 
      VALUES(2, 10, 'closed', TO_DATE('01/01/2019', 'DD/MM/YYYY'));
      INSERT INTO operations(maintenance_id, operation_id, status, operation_date) 
      VALUES(2, 20, 'closed', TO_DATE('01/03/2019', 'DD/MM/YYYY'));
      INSERT INTO operations(maintenance_id, operation_id, status, operation_date) 
      VALUES(2, 30, 'open', TO_DATE('01/04/2019', 'DD/MM/YYYY'));
      INSERT INTO operations(maintenance_id, operation_id, status, operation_date) 
      VALUES(2, 40, 'open', TO_DATE('01/05/2019', 'DD/MM/YYYY'));
      

       

      Result should not include this maintenance_id.

       

       

      Problem is that we can have a sample like this:

       

      INSERT INTO operations(maintenance_id, operation_id, status, operation_date) 
      VALUES(3, 10, 'open', TO_DATE('01/01/2019', 'DD/MM/YYYY'));
      INSERT INTO operations(maintenance_id, operation_id, status, operation_date) 
      VALUES(3, 11, 'closed', TO_DATE('01/09/2019', 'DD/MM/YYYY'));
      INSERT INTO operations(maintenance_id, operation_id, status, operation_date) 
      VALUES(3, 12, 'something', TO_DATE('01/02/2019', 'DD/MM/YYYY'));
      INSERT INTO operations(maintenance_id, operation_id, status, operation_date) 
      VALUES(3, 13, 'foo', TO_DATE('01/02/2019', 'DD/MM/YYYY'));
      -- variable number of rows.
      INSERT INTO operations(maintenance_id, operation_id, status, operation_date) 
      VALUES(3, 11, 'open', TO_DATE('01/05/2019', 'DD/MM/YYYY'));
      

       

      Multiple tasks between the closed and the open...

       

      Any idea if this can be done in SQL in Oracle 11g?

       

       

      Regards,

        • 1. Re: Comparing with following rows
          Solomon Yakobson

          Something like:

           

          with t as (

                     select  maintenance_id,

                             case

                                when     -- operation is last operation

                                         max(operation_id) over(partition by maintenance_id) = operation_id

                                     and -- and status is open

                                         status = 'open'

                                     and -- and previous operation statusis closed

                                         lag(status) over(partition by maintenance_id order by operation_id) = 'closed'

                                     and -- and previous operation date is > operation date

                                         lag(operation_date) over(partition by maintenance_id order by operation_id) > operation_date

                                  then 1

                             end flag

                       from  operations

                       where status in (

                                        'open',

                                        'closed'

                                       )

                    )

          select  maintenance_id

            from  t

            where flag = 1

          /

           

          For example:

           

          SQL> select  *

            2    from  operations

            3  /

           

          MAINTENANCE_ID OPERATION_ID STATUS     OPERATION

          -------------- ------------ ---------- ---------

                       1           10 open       01-JAN-19

                       1           12 closed     01-MAR-19

                       1           13 open       01-FEB-19

                       2           10 closed     01-JAN-19

                       2           20 closed     01-MAR-19

                       2           30 open       01-APR-19

                       2           40 open       01-MAY-19

                       3           10 open       01-JAN-19

                       3           11 closed     01-SEP-19

                       3           12 something  01-FEB-19

                       3           13 foo        01-FEB-19

                       3           11 open       01-MAY-19

           

          12 rows selected.

           

          SQL> with t as (

            2             select  maintenance_id,

            3                     case

            4                        when     -- operation is last operation

            5                                 max(operation_id) over(partition by maintenance_id) = operation_id

            6                             and -- and status is open

            7                                 status = 'open'

            8                             and -- and previous operation statusis closed

            9                                 lag(status) over(partition by maintenance_id order by operation_id) = 'closed'

          10                             and -- and previous operation date is > operation date

          11                                 lag(operation_date) over(partition by maintenance_id order by operation_id) > operation_date

          12                          then 1

          13                     end flag

          14               from  operations

          15               where status in (

          16                                'open',

          17                                'closed'

          18                               )

          19            )

          20  select  maintenance_id

          21    from  t

          22    where flag = 1

          23  /

           

          MAINTENANCE_ID

          --------------

                       1

                       3

           

          SQL>

           

          SY.

          1 person found this helpful
          • 2. Re: Comparing with following rows
            Christopher Jothi

            Hi ,

             

            You may try using the ORDER BY clause with different Sort Orders as Below.

             

            SELECT operation_id , status , operation_date

            FROM

            operations

            ORDER BY operation_id , status ASC , operation_date DESC

             

            This will list Operation_Id and Status is Ascending Order (like 10 Closed first and 10 Open Next). Operation Date in Descending Order.

             

            Records:

            maintenance_id, operation_id, status, operation_date

            1,10,Open,01/01/2019

            1,12,Closed,01/03/2019

            1,13,Open,01/02/2019

            2,10,Closed,01/01/2019

            2,20,Closed,01/03/2019

            2,30,Open,01/04/2019

            2,40,Open,01/05/2019

            3,10,Open,01/01/2019

            3,11,Closed,01/09/2019

            3,12,'Something',01/02/2019

            3,13,'Foo',01/02/2019

            3,11,'Open',01/05/2019

             

            Sorted Records:

            Closed,10,01/01/2019

            Open,10,01/01/2019

            Open,10,01/01/2019

            Closed,11,01/09/2019

            Open,11,01/05/2019

            Closed,12,01/03/2019

            Something,12,01/02/2019

            Open,13,01/02/2019

            Foo,13,01/02/2019

            Closed,20,01/03/2019

            Open,30,01/04/2019

            Open,40,01/05/2019

             

            Regards,

            Christopher.

            • 3. Re: Comparing with following rows
              Frank Kulash

              Hi,

               

              Thanks for posting the sample data.  It helps if you also post the exact results you want from the given sample data.  It's good to describe the results, like you did, but describe the results in addition to, not instead of, actually posting them.

               

              So you want to find maintenance_ids where

              there is at least one row O with status = 'open', and

              there is at least one row C with status = 'closed', and an lower operation_id than O, and a greater operation_date than )

              Rows with other statuses are ignored in this problem.

              Is that right?

               

              Here's one way to do that:

              SELECT DISTINCT  maintenance_id

              FROM      operations  o

              WHERE     status  = 'open'

              AND       EXISTS (

                                   SELECT  NULL

                                   FROM    operations  c

                                   WHERE   c.status          = 'closed'

                                   AND     c.maintenance_id  = o.maintenance_id

                                   AND     c.operation_id    < o.operation_id

                                   AND     c.operation_date  > o.operation_date

                               )

              ;

              1 person found this helpful
              • 4. Re: Comparing with following rows
                user13117585

                Good evening sirs,

                 

                Thank you for your suggestions. I will take a momentto understand them. Now I have ideas how to proceed.

                 

                Regards