Find all records that never had status = SUCCESSFUL

I have some processes running within the backend and we are logging it in DB with its status. Now I need to find all processes that never ran successfully since last month.

Example table:

PID        executiondate  Status
1          2021-06-15     FAILED.       <- PID 1 was successful, but in last month 
1          2021-06-05     FAILED
1          2021-06-01     FAILED
1          2021-05-01     SUCCESSFUL  
2          2021-06-15     SUCCESSFUL    <- PID 2 was successful failed within month
2          2021-06-05     FAILED
2          2021-06-01     SUCCESSFUL
3          2021-06-15     FAILED        <- PID 3 only fails
3          2021-06-05     FAILED
3          2021-06-01     FAILED
4          2021-06-15     SUCCESSFUL    <- PID 4 only successful
4          2021-06-05     SUCCESSFUL
4          2021-06-01     SUCCESSFUL

Condition:

• I am running a query on 16-06-2021
• To Find only failed PIDs last month (16-06-2021 to 16-05-2021)

Expected Result:

• PID 1 and PID 3

My try:

• Tried to get the count of status for each PID and then findings record having 1 count and failed status. But it did not work.

   WITH dataset_by_status AS
   (
            SELECT   pid,
                 Count(*) AS counter,
                 status
            FROM     exampletable
            WHERE    (
                          status = 'FAILED'
                 OR       status = 'SUCCESSFUL')
            AND      executiondate >= (Now() - interval '1 MONTH')
            GROUP BY status
            ORDER BY pid )
   SELECT   pid,
            count(*) AS counter
   FROM     dataset_by_status
   WHERE    status = 'FAILED'
   GROUP BY pid
   HAVING   count(*) = 1
  

Answer

You want all PIDs that have no ‘SUCCESSFUL’ in the last ~30 days. To find them, aggregate per PID and make sure you don’t find such a row for it.

select pid
from exampletable
group by pid
having count(*) filter (where status = 'SUCCESSFUL'
                          and executiondate >= now() - interval '1 month') = 0;

If you want to add a counter for the failures in the date range:

select pid, count(*) filter (where status = 'FAILED'
                             and executiondate >= now() - interval '1 month') as failed

If on the other hand, you want to restrict this to PIDs that have entries in the last ~30 days, move the date range condition to WHERE:

select pid, count(*) filter (where status = 'FAILED') as failed
from exampletable
where executiondate >= now() - interval '1 month'
group by pid
having count(*) filter (where status = 'SUCCESSFUL') = 0;