SQL Query Syntax: How to Tell SQL to Pull a Record (Once again, this should be easy; but, the app wants to make it hard)

Hello:

I must have upset someone in a previous life, because this current life is a real purgatory.

Once again, SQL query syntax which should be very easy to code is not working.

Please see the first code block below.

All that I'm flippin' trying to do is pull in a record where the RM20201.APFRMDCNM is not in the RM20101.DOCNUMBR field for the CUSTNMBR field of both the RM20101 and RM20201 tables.  True, as you can see from the embedded screenshot below, the RM20201.APTODCNM field is indeed in the RM20101.DOCNUMBR field, for the CUSTNMBR of either table.  

But, trust me.  In RM20201 table, the APFRMDCNM field is not in the RM20101.DOCNUMBR field for this CUSTNMBR (i.e. this customer).

By the way, the second code block below is what generates what is seen in the screenshot.  RM20101 represents the table of the first record in the screenshot, while RM20201 is the second record.

Again, I simply want to pull the record for the RM20201.APFRMDCNM field where it does not exist in the RM20101.DOCNUMBR field for a CUSTNMBR of both tables.  How do I update my first code block to do so?

Thank you, so much!

John

DECLARE @AGE DATETIME;
DECLARE @RUN DATETIME;
SET @AGE = '2015-09-30 00:00:00.000';
SET @RUN = '2016-07-31 00:00:00.000';

select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName], RM20201.APFRDCNM as [DocumentNumber],
0 as Cnt,
CASE WHEN RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE
AND RM20201.APTODCDT < @AGE
AND RM20201.APFRDCNM not in (SELECT RM20101.DOCNUMBR FROM RM20101 INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM
where RM20101.CUSTNMBR = RM20201.CUSTNMBR
and
RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE)
THEN RM20201.APPTOAMT * -1
ELSE 0 END
as [OPEN A/R]
from RM20201
INNER JOIN RM20101 ON RM20201.CUSTNMBR = RM20101.CUSTNMBR AND RM20201.APFRDCNM = RM20101.DOCNUMBR
INNER JOIN RM00101 on RM20101.CUSTNMBR = RM00101.CUSTNMBR
LEFT OUTER JOIN CN00500 ON RM20101.CUSTNMBR = CN00500.CUSTNMBR
WHERE (RM20101.VOIDSTTS = 0) and (RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', '0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981'))     
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME, RM00101.PYMTRMID, CN00500.CRDTMGR, RM00101.COMMENT1, RM00101.COMMENT2, 
RM20101.DOCNUMBR, RM20101.DOCDATE,
RM20101.RMDTYPAL, RM20101.ORTRXAMT, RM20101.CURTRXAM, RM20201.DATE1, RM20101.DUEDATE, RM20201.APTODCDT, 
RM20201.APFRDCTY, RM20201.APFRDCDT, RM20201.APPTOAMT, RM20201.CUSTNMBR, RM20201.APFRDCNM, RM20201.APTODCTY,
RM20201.APFRMAPLYAMT, RM20201.APTODCNM
HAVING 
CASE WHEN RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE
AND RM20201.APTODCDT < @AGE
AND RM20201.APFRDCNM not in (SELECT RM20101.DOCNUMBR FROM RM20101 INNER JOIN RM20201 ON RM20101.CUSTNMBR = RM20201.CUSTNMBR AND RM20101.DOCNUMBR = RM20201.APFRDCNM
where RM20101.CUSTNMBR = RM20201.CUSTNMBR
and
RM20201.APFRDCTY > 6  
AND RM20201.DATE1 = @AGE 
AND RM20201.APFRDCDT = @AGE)
THEN RM20201.APPTOAMT * -1
ELSE 0 END
<> 0 

Select allOpen in new window

select * from RM20101 where CUSTNMBR  = '179520' and ORTRXAMT = 180

select * from RM20201 where CUSTNMBR = '179520' and APTODCNM = 'G02010305'

Select allOpen in new window