following issue:
I want to compare the rows within a table with each other.
Let's assume I have a testcase table with below data constellation:
ID Result Date
123 success 29.04.2021
123 error 28.04.2021
123 success 27.04.2021
123 success 26.04.2021
123 error 25.04.2021
234 success 29.04.2021
234 success 28.04.2021
234 success 27.04.2021
234 success 26.04.2021
234 error 25.04.2021
I want my query to return only those ID's where the result was identical for the last 3 days. But I want to handle the days parameter dynamically without using PL/SQL. That mean's - if I need to compare the last 5 days I just want to change the paramter in my sql statement. Can that be realized?
Back to my example above:
I want my query to give back:
ID
234
Best regards
You can use just simple group by + having clause:
select id
from t
where dt>=sysdate-3
group by id
having count(distinct result)=1 -- just one distinct `result`
and count(*)=3 -- only 3 rows >= sysdate-3
and count(distinct dt)=3 -- all 3 days are present;
Full test cast with test data:
with t(ID, Result, Dt) as (
select 123, 'success' , to_date('29.04.2021','dd.mm.yyyy') from dual union all
select 123, 'error ' , to_date('28.04.2021','dd.mm.yyyy') from dual union all
select 123, 'success' , to_date('27.04.2021','dd.mm.yyyy') from dual union all
select 123, 'success' , to_date('26.04.2021','dd.mm.yyyy') from dual union all
select 123, 'error ' , to_date('25.04.2021','dd.mm.yyyy') from dual union all
select 234, 'success' , to_date('29.04.2021','dd.mm.yyyy') from dual union all
select 234, 'success' , to_date('28.04.2021','dd.mm.yyyy') from dual union all
select 234, 'success' , to_date('27.04.2021','dd.mm.yyyy') from dual union all
select 234, 'success' , to_date('26.04.2021','dd.mm.yyyy') from dual union all
select 234, 'error ' , to_date('25.04.2021','dd.mm.yyyy') from dual
)
select id
from t
where dt>=sysdate-3
group by id
having count(distinct result)=1 -- just one distinct `result`
and count(*)=3 -- only 3 rows >= sysdate-3
and count(distinct dt)=3 -- all 3 days are present
;
Results:
ID
----------
234
Modified query as per a clarification in the comment above:
I want to check last 3 days starting from today. If I only have two entries then I would like to compare only two entries...
select id
from t
where dt>=sysdate-3
group by id
having count(distinct result)=1 -- just one distinct `result`
;
Full test case:
with t(ID, Result, Dt) as (
select 123, 'success' , to_date('29.04.2021','dd.mm.yyyy') from dual union all
select 123, 'error ' , to_date('28.04.2021','dd.mm.yyyy') from dual union all
select 123, 'success' , to_date('27.04.2021','dd.mm.yyyy') from dual union all
select 123, 'success' , to_date('26.04.2021','dd.mm.yyyy') from dual union all
select 123, 'error ' , to_date('25.04.2021','dd.mm.yyyy') from dual union all
select 234, 'success' , to_date('29.04.2021','dd.mm.yyyy') from dual union all
select 234, 'success' , to_date('28.04.2021','dd.mm.yyyy') from dual union all
select 234, 'success' , to_date('27.04.2021','dd.mm.yyyy') from dual union all
select 234, 'success' , to_date('26.04.2021','dd.mm.yyyy') from dual union all
select 234, 'error ' , to_date('25.04.2021','dd.mm.yyyy') from dual
)
select id
from t
where dt>=sysdate-3
group by id
having count(distinct result)=1 -- just one distinct `result`
;
----------------------
--Results:
ID
----------
234
PS. It's better to give test data in this format:
with t(ID, Result, Dt) as (
select 123, 'success' , to_date('29.04.2021','dd.mm.yyyy') from dual union all
select 123, 'error ' , to_date('28.04.2021','dd.mm.yyyy') from dual union all
select 123, 'success' , to_date('27.04.2021','dd.mm.yyyy') from dual union all
select 123, 'success' , to_date('26.04.2021','dd.mm.yyyy') from dual union all
select 123, 'error ' , to_date('25.04.2021','dd.mm.yyyy') from dual union all
select 234, 'success' , to_date('29.04.2021','dd.mm.yyyy') from dual union all
select 234, 'success' , to_date('28.04.2021','dd.mm.yyyy') from dual union all
select 234, 'success' , to_date('27.04.2021','dd.mm.yyyy') from dual union all
select 234, 'success' , to_date('26.04.2021','dd.mm.yyyy') from dual union all
select 234, 'error ' , to_date('25.04.2021','dd.mm.yyyy') from dual
)
select *
from t;