Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

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
    from(
select acct_id,test_date,testid,sum(testid1and4) over(partition by acct_id) testid1and4, sum(testid4and21) over(partition by acct_id) testid4and21
    from(
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:

ACCT_ID 	TEST_DATE 	TESTID 	TESTID1AND4 	TESTID4AND21
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,

JVS

This post has been answered by Frank Kulash on Oct 19 2023
Jump to Answer
Comments
Post Details
Added on Oct 19 2023
5 comments
197 views