HELP WITH MY WHERE CLAUSE REQUIREMENT

[mjsystemss]

Hello ,

I need help with examining this query.

I have this query below and my requirements. The First Requirement is

1. If appselection !='notice to process' and notetxt is NULL in last month show acct and no txt notes

2. if appselection='notice to process' and no notetxt in one month show the results (acctid)

---------------------------------------------------------------------------------------

WITH  NOTATION AS (SELECT

acctid,

appselection,

notetxt,xtp,

ndate

FROM TABLEA),

City as (acctid,

appselection

FROM EXAMPLE)

SELECT a.acctid,b.appselection,a.notetxt,a.ndate,a.xtp

FROM NOTATION a

LEFT JOIN b

ON a.acctid=b.acctid

WHERE b.appselection='notice to process' AND (a.notetxt IS NULL OR TO_DATE(a.ndate, 'mm/dd/yyyy')<=ADD_Months(SYSDATE,-1))) OR b.appselection='notice to process' AND (a.notetxt IS NULL OR TO_DATE(a.ndate,'mm/dd/yyyy')<=ADD_MONTHS(sysdaate,-1)))

-------------------------------------------------------------------------------------

I need another Where clause for requirement 2 using the same query above

REQUIREMENT b: show acctid with no notetxt in Last 2 weeks. below is what I have

WHERE n.notetxt IS NULL AND TO_DATE(n.ndate, 'mm/dd/yyyy')<=CURREN_DATE-14)

[#2]

Do you need two different result set? With requirement 1, show acct and notetext. With requirement 2, show acctid.
In your SQL sample, WHERE clause has two same parts of conditions that split by 'OR', what is the purpose?

Give some sample rows and show the result your want, will supply more help to understand your question.

[#3]

@rodgerkong 

The Two conditions are to produce one result set.

The resulting table should be like this;

ACCTID            NDATE             NOTETXT                  APPSTATUS

000001        02/06/2023          FIRE INCIDENT         NO NOTICE

000002        03/09/2024          MINOR FIRE             NOTICE TO PROCESS

000003        09/09/2024          NO INCIDENT          NOTICE TO PROCESS

000004        03/07/2024                                           NO NOTICE

[#4]

What are the structures of TABLEA and EXAMPLE? And give some sample data in those 2 tables.
Base on the sample data, what is the result you want?

[#5]

The Table I have given is a consolidated Table and the table should be filtered based on the requirements below.
acctid              number
ndate              varchar2(20)
Notetxt           Varchar2(50)
AppStatus       Varchar2(100)
1. If appstatus !='notice to process' and no notetext in last month show acct and no txt notes

2. if appstatus='notice to process' and no notetext in one month show the results (acctid)

[#6]

CREATE TABLE TABLEA
(ACCTID NUMBER(15) PRIMARY KEY,
NDATE Varchar2(25),
NOTETXT VARCHAR2(50),
APPSTATUS VARCHAR2(50));
ASSUME DATA IS INSERTED using below insert sttements.
Insert into TableA Values (acctid,ndate,notetxt,AppStatus)
(1,'2/6/2023','fire incident', ‘no notice’);
Insert into TableA Values
(2,'3/9/2024','minor fire', ‘notice to process’);
Insert into TableA Values
(3,'9/9/2024','no incident', ‘notice to process’);
Insert into TableA Values
(4,'3/7/2024',' ', ‘notice to process’);
--Below is the select statement
SELECT ACCTID, NDATE,NOTETXT,APPSTATUS
FROM TABLEA
WHERE b.appselection='notice to process' AND (a.notetxt IS NULL OR TO_DATE(a.ndate, 'mm/dd/yyyy')<=ADD_Months(SYSDATE,-1))) OR b.appselection='notice to process' AND (a.notetxt IS NULL OR TO_DATE(a.ndate,'mm/dd/yyyy')<=ADD_MONTHS(sysdaate,-1)));
Requirement;
I need a where clause that meets the below requirements
If appstatus !='notice to process' and no notetext in last month show acct and no txt notes.
if appstatus='notice to process' and no notetext in one month show the results (acctid).
I need to know if my where clause addresses my requirement well.

[#7]

No. I need One result set. Show account acctid that meets each requirement.
I used OR to indicate that if condition 1 is met show acctid, if condition 2 is met show acctid. Both conditions must not be true to show acct id because they are not the same. It is the requirement that I need to code with look at the table.
SELECT ACCTID, NDATE,NOTETXT,APPSTATUS
FROM TABLEA;
This is a sample select statement that my requirement will be based on.
ACCTID NDATE NOTETXT APPSTATUS
------ ---------- --------------- --------------------
1 2/6/2023 FIRE INCIDENT NO NOTICE
2 3/9/2024 MINOR FIRE NOTICE TO PROCESS
3 9/9/2024 NO INCIDENT NOTICE TO PROCESS
4 3/7/2024 NOTICE ON HOLD
my interest is on the records with 'notice to process' . Note: Notice to process is the same as application complete. If the requirement is coded properly it will address the given requirement. The actual record is over a million records.

[#8]

this info help full to my knowladge