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).
LVL 1
rwheeler23Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Leo TorresSQL DeveloperCommented:
Try

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

Open in new window

dsackerContract ERP Admin/ConsultantCommented:
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.
Mike EghtebasDatabase and Application DeveloperCommented:
create table #DOC (Docnumber varchar(15), docdate varchar(10), docamount int, doctype smallint);
create table #INV (Docnumber varchar(15), docdate varchar(10), docamount int);
insert into #DOC(Docnumber, docdate, docamount, doctype) values 
('CD1000','03/31/15', 1000, 7),
('RMV 100-02','03/25/15', 20000, 7),
('100-02','01/15/15', 50000, 1)

insert into #INV(Docnumber, docdate, docamount) values 
('100-02','01/15/15', 50000)

select * from #DOC;
select * from #INV;

Open in new window


SELECT Docnumber, docdate, docamount, doctype 
From #DOC As D
Where Not Exists(Select * From #INV As I
       Where D.Docnumber like '%' + I.Docnumber)
       And doctype =7;

Open in new window


This produces:
CD1000      03/31/15      1000      7

Here is your version of the code:
SELECT Docnumber, docdate, docamount 
From RM20101 As R
Where Not Exists(Select * From PA23100 As P
       Where R.Docnumber like '%' + P.Docnumber)
       And doctype =7;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwheeler23Author Commented:
Yes it does, I was so close. A fresh set of eyes is always helpful . Thank you!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.