I have a dilemma and I am trying to figure out to structure the script. Here is a sample data set.
There first table contains A/R documents which includes invoices and credit memos
The second table contain project accounting invoices.
The problem I have is that there are two types of credit memos. Credit memos that tie to project invoices and credit memos that do not.
RM20101 Docnumber docdate docamount doctype
CD1000 03/31/15 1,000 7
RMV 100-02 03/25/15 20,000 7
100-02 01/15/15 50,000 1
PA23100 Docnumber docdate docamount
100-02 01/15/15 50,000
So in this case doctype = 7 means credit memo and 1 means invoice
The dilemma is how to I structure the sql query so that it will only return the first doc CD1000? The second RMV 100-02 needs to be excluded because the 100-02 substring from the PA23100 table exists in the RM20101.DOCNUMBR. So what I am looking for are credit memos that only exist in the PM20101 table and are not project related (PA23100).