Skip to Main Content



For appeals, questions and feedback, please email

checking for values in window range

v sOct 19 2023

Hi Gurus,

Please find below create table and sample data:

create table test_accts(acct_id number, test_date date, testid number);

insert into test_accts values(101,sysdate,1);
insert into test_accts values(101,sysdate,4);
insert into test_accts values(101,sysdate,21);
insert into test_accts values(101,sysdate,22);

insert into test_accts values(102,sysdate,1);
insert into test_accts values(102,sysdate,3);

insert into test_accts values(103,sysdate,1);
insert into test_accts values(103,sysdate,3);
insert into test_accts values(103,sysdate,4);

insert into test_accts values(104,sysdate,1);
insert into test_accts values(104,sysdate,21);

insert into test_accts values(105,sysdate,4);
insert into test_accts values(105,sysdate,21);

considering the above sample records, I need two derived fields

  • field 1 will have value true if the acct id has records with testid 1 and 4 for the same test_date
  • field 2 will have value true if the acct id has records with testid 4 and 21 for the same test_date
  • there could be other such combinations as well

I have used the below query, is there a better way to do it using analytical functions or otherwise, I am not able to think through a solution using analytical functions.

select acct_id,test_date,testid,decode(testid1and4,2,'True','False') testid1and4 ,decode(testid4and21,2,'True','False') testid4and21
select acct_id,test_date,testid,sum(testid1and4) over(partition by acct_id) testid1and4, sum(testid4and21) over(partition by acct_id) testid4and21
select acct_id,test_date,testid,case when testid = 1 then 1 when testid = 4 then 1 else null end testid1and4, case when testid=4  then 1 when testid=21 then 1 else null end testid4and21
from test_accts order by acct_id));

output using the above query:

101 		19-OCT-23 		1 		True 		True
101 		19-OCT-23 		4 		True 		True
101 		19-OCT-23 		21 		True 		True
101 		19-OCT-23 		22 		True 		True
102 		19-OCT-23 		1 		False 		False
102 		19-OCT-23 		3 		False 		False
103 		19-OCT-23 		1 		True 		False
103 		19-OCT-23 		4 		True 		False
103 		19-OCT-23 		3 		True 		False
104 		19-OCT-23 		1 		False 		False
104 		19-OCT-23 		21 		False 		False
105 		19-OCT-23 		4 		False 		True
105 		19-OCT-23 		21 		False 		True

Thanks for your help.

Best Regards,


This post has been answered by Frank Kulash on Oct 19 2023
Jump to Answer
Post Details
Added on Oct 19 2023