I need to select all values from column 'Name' where value in 'Result' column is continuous 'Pass' for last 7 days for a particular 'Environment'.

Table structure is as following:

In this case, I will get 'ABC' and 'PQR' as response for 'Environment' UAT, as XYZ failed in last 7 days.

CodePudding user response：

I think it is as simple as this:

select name, environment
from t
where timestamp >= current_date - interval '7 days'
group by name, environment
having count(case when result = 'Fail' then 1 end) = 0

Note that current_date - interval '7 days' needs to be changed to 6 days or current_timestamp depending on your definition of 7 days.

CodePudding user response：

You're looking for something like this:

SELECT name,environment
FROM t 
GROUP BY name,environment
HAVING COUNT(*) 
  FILTER (WHERE (result = 'Fail' OR result IS NULL) AND                 
                 timestamp::date >= CURRENT_DATE-7) = 0;

 name | environment 
------ -------------
 ABC  | UAT
 PQR  | UAT
(2 rows)

Basically you list nameand environment, and then count how many records match the criteria you want to exclude, namely result = 'Fail' within the last 7 days (from current date). Those with count 0 are the records you're looking for.

Demo: db<>fiddle

CodePudding user response：

You can use below query, simplified using row_number(),

select name, result, timestamp, environment from
(select name, result, timestamp, environment,
row_number() over(partition by result, environment order by timestamp) as rnk
 from table
where timestamp > CURRENT_DATE - interval '7 days'
) where rnk = 7 and result = 'Pass';

Records are selected for 7 days and row_number is returned with respect to result and environment sorted by timestamp and filtering the records having continuous value of Pass for 7 days