asked on

SQL Syntax to filter credit memos

I have a dilemma and I am trying to figure out to structure the script. Here is a sample data set.

RM20101.DOCNUMBR,DOCDATE,DOCAMOUNT,DOCTYPE
PA23100.DOCNUMBR,DOCDATE,DOCAMOUNT

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.

Sample Data:
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).

Leo Torres

Try

SELECT * FROM Sampletable st
WHERE NOT EXISTS (SELECT TOP 1 1 FROM MyotherTable mt
				WHERE mt.SomeKey = st.SomeKey)

Open in new window

dsacker

Wouldn't the third record also need to be excluded? If so, you might want to do your query as follows:

SELECT a.Docnumber, a.docdate, a.docamount, a.doctype
FROM   RM20101 a
LEFT JOIN (SELECT DISTINCT Docnumber FROM PA23100) b
ON     b.Docnumber = RIGHT(a.Docnumber, LEN(b.Docnumber))
WHERE  b.Docnumber IS NULL

Open in new window

I'm joining on the assumption that the rightmost characters of RM20101.Docnumber are what you want to match against PA23100.Docnumber.

ASKER CERTIFIED SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

Yes it does, I was so close. A fresh set of eyes is always helpful . Thank you!