How to filter out results from PostgreSQL query based on combination of criteria across multiple columns

Question

I am trying to find unfinished tasks within a PostgreSQL table of tasks based on criteria.

The table contains information on the task number, step within a task, type, workplace, time and operator. The task is considered finished when all operators finished. What I specifically need to achieve is a query result that would show all relevant rows for a task and within it the rows that only exist with a "start" code. Example follows:

Task number Step    Code    Type    Workplace   Operator    Time
5006302212  10      Start   PPBG    M7UF-110    18003461    05:55:14
5006302212  10      Start   PPBG    M7UF-110    18002838    05:59:56
5006302212  10      Stop    PPBG    M7UF-110    18002838    13:59:23
5006302212  10      Start   PPBG    M7UF-110    18003460    05:55:04

From this example I see that task 5006302212 and its step 10 was started (code Start) by 3 operators. One of them already finished (code Stop). What I need is to get this table:

Task number Step    Code    Type    Workplace   Operator    Time
5006302212  10      Start   PPBG    M7UF-110    18003461    05:55:14
5006302212  10      Start   PPBG    M7UF-110    18003460    05:55:04

I tried using EXCEPT for a query of Stop code but that won't help as it returns the row with Start code for operator that already finished.

Any help will be appreciated.

Jeremy · Accepted Answer · 2019-10-15 12:19:20Z

1

You can use the NOT EXISTS clause to find rows that don't have a matching Stop step:

SELECT * FROM task 
WHERE code = 'Start' 
  AND NOT EXISTS 
  (
    SELECT FROM task t2
    WHERE (task.task_number, task.step, task.type, task.workplace, task.operator) =
         (t2.task_number, t2.step, t2.type, t2.workplace, t2.operator)
      AND t2.code = 'Stop'
);

Here's a fiddle

Edit: In response to the comment, it gets a bit trickier if the task can be restarted by the same operator, but you should be able to just check for Stop times > Start times in the AND NOT EXISTS clause:

SELECT * FROM task 
WHERE code = 'Start' 
  AND NOT EXISTS 
  (
    SELECT FROM task t2
    WHERE (task.task_number, task.step, task.type, task.workplace, task.operator) =
         (t2.task_number, t2.step, t2.type, t2.workplace, t2.operator)
      AND t2.code = 'Stop'
      AND t2.task_time > task.task_time
);

Fiddle2

edited Oct 15, 2019 at 12:19

answered Oct 14, 2019 at 15:03

Jeremy

6,77322 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Vajda Over a year ago

Thank you, Jeremy, this helped me a lot and I am almost there. The last thing I need to solve is if the operator "restarts" his/her work with the Start code again. That means there will be originally three rows for the same operator (Start, Stop, Start) and I would like to filter it to get only the latest Start row. Updated fiddle

Jeremy Over a year ago

@PeterVajda I updated the answer with a solution that should work for your updated case.

Collectives™ on Stack Overflow

How to filter out results from PostgreSQL query based on combination of criteria across multiple columns

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related