Skip to content
Advertisement

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
    
    

Advertisement

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;

User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement