T-SQL How to Find the Value in One Field Based on the Same Value in Another
Posted on 2011-09-29
I have a table called RM20201. That table contains accounts receivable documents showing payments, debit memos, and credit memos applied to invoices.
I need to extract an invoice number from this table based on the payment number that is applied to that invoice and the payment's corresponding debit memo.
The field for payment (APFRDCNM) is going to be the same value for the invoice and the debit memo.
The field for the invoice and the debit memo is APTODCNM. The field for the "document type" for the invoice is APTODCTY. That value is "1" for the invoice's document type.
Below are the values for the invoice, the debit memo, and the payment, respectively:
Again, both the invoice and the debit memo contain the same payment number of PYMNT00027374.
I have tried to develop a simple "select" statement that will ultimately give me the value "INV00031038". Again, this is the invoice. That's what I want.
I tried doing this in various ways such as with COUNT, HAVING, etc. But, no luck.
Conceivably, the document type that I mentioned can be used in the select statement to pull in the invoice that I need. But, I don't know how to make this all work.