Help greatly appreciated with formatting a SQL Query
I need to select from an ARDOC table Credit Memo documents that are older than the oldest Invoice in the same table.
Both of which have a DOCBAL<>0 and for the same CUSTID.
I am basically identifying Credit Memos with DOCBAL <>0 that are older than the oldest Invoice with a DOCBAL <>0 within a CUSTID.
One document per row in the table. Table Contains both CM and IN type entries.
Credit Memos are DOCTYPE='CM'
Invoices are DOCTYPE='IN'
Here are a couple of queries I have:
Your help in formatting these to yield the results is truly appreciated:
select CustId,RefNbr,DocType,DocDate,OrigDocAmt,DocBal
from ARDoc
where DocType = 'CM'
and DocBal<>0
select CustId,RefNbr,DocType,DocDate,OrigDocAmt,DocBal
from ARDoc
where DocType = 'IN'
and DocBal<>0