Solved

T-SQL How to Find the Value in One Field Based on the Same Value in Another

Posted on 2011-09-29
14
633 Views
Last Modified: 2012-05-12
Hello:

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:

INV00031038
DEBIT00000145
PYMNT00027374

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.

Ideas?

Apitech
0
Comment
Question by:apitech
  • 7
  • 5
  • 2
14 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 36816463
Just to clarify, you want to find an invoice # (found in APTODCNM), based on a provided payment number (stored in APFRDCNM ) where a debit memo also exists?
SELECT APTODCNM FROM RM20201 WHERE APFRDCNM = <Payment #> AND EXISTS (SELECT 1 FROM RM20201 as A WHERE APFRDCNM = RM20201.APFRDCNM)

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36816519
do you mean this ?

select i.apfrdcnm
from rm20201 as p
inner join (select * from rm20201
   where aptodcty=1)
as i
on p.apfrdcnm=i.apfrdcnm
where p.apfrdcnm-xxxx

     
      if not polease supply and example of the rows in the table (are there 3?)
0
 
LVL 1

Author Comment

by:apitech
ID: 36816795
OK.  The query should not reference a specific payment number.  True, I gave an example of a specific payment number.  But, the query needs to be more dynamic that that.  The payment number could be anything.

I just want to be able to extract the APTODCNM for an invoice where the APFRDCNM is the same as the APFRDCNM for the debit memo (which is also an APTODCNM).

I'm surprised that there are no articles online that teach how to extract a value based on another vlaue for that same record where the value matches another record.  

Apitech
0
 
LVL 1

Author Comment

by:apitech
ID: 36816852
Shaun Kline's script works well.  But, there is no telling ongoing what the specific payment number is going to be.  If that script could be made to be dynamic, that woudl be great!  :)
0
 
LVL 1

Author Comment

by:apitech
ID: 36816878
I think that I may have it, based on what Shaun gave me.  Does this work?  It seems to.

select APTODCNM FROM RM20201 where APFRDCNM = RM20201.APFRDCNM and APTODCTY = '1'
ORDER BY APTODCNM
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36816983
the articles you want will be talking about joins or self joins for a table.

no
select APTODCNM FROM RM20201 where APFRDCNM = RM20201.APFRDCNM and APTODCTY = '1'
ORDER BY APTODCNM

all the statement is doing is comparing the column to itself,,

your getting invoices purely because of the  aptodcty condition.


if you want to relate the references together you need to join the table to itself ,,,
so that you can display the relevant data from the related columns...

select i.aptodcnm,d.aptodcnm
from rm20201 as i
inner join rm20201 as d
 on i.apfrdcnm=d.apfrdcnm
where i.aptodcty=1
and d.aptodcty = whatever value indicates a debit....
0
 
LVL 1

Author Comment

by:apitech
ID: 36817152
I'm afraid that I'm still not getting anywhere.  Let me give some background on what I'm trying to accomplish.

The table RM20201 is one where you can have a debit memo "attached" to an invoice (sort of).  And, both documents are in the same field--APTODCNM,  I need that invoice number "in" a big query that I have developed as I need for the query to grab a field called ME50405.USERDEF1.  In my query that I have pasted below, I have called this field "PropertyManagement".  

PropertyManagement is not attached to debit memos--only invoices.  My query, irconically, reports on just debit memos but I need the field PropertyManagement to display and, again, this field only comes from the debit memo's corresponding invoice.

The only way that I know to pull the invoice number, so that I can get PropertyManagement, is to tie it to the only field that it and the debit memo have in common--the payment number field.  If there were another way of doing it, I would simply do it.

So, I want to embed the query that I'm asking for here on this forum into my big query so that I can get my debit memos plus the PropertyManagement field.

My query is and always has been successfully pulling the debit memos.  But, I cannot get this blame PropertyManagement field because the field is only attached to invoices.  

I placed Lowfatspread's query into my query.  But, it is returning 0 results.  Again, my query without that "subquery" or whatever in the world it's called does return results--it just does not display the PropertyManagement field.

Helpl!!

select
CASE WHEN OAKVA..GL20000.SOURCDOC IN ('PMTRX', 'GJ')
THEN CONVERT(VARCHAR(20), OAKVA..GL20000.JRNENTRY)
WHEN OAKVA..GL20000.SOURCDOC = 'SJ' THEN
OAKVA..GL20000.ORCTRNUM ELSE '' END as [DocumentNumber],
OAKVA..RM20101.DOCDATE as [DocumentDate],
OAKVA..GL20000.TRXDATE as [PostDate],
LEFT(OAKVA..RM20101.CUSTNMBR, 3) as [ClientCode],
OAKVA..RM20101.CUSTNMBR as [ClientNumber],
OAKVA..RM00101.CUSTNAME as [ClientName],
CASE WHEN LEFT(OAKVA..GL20000.ORCTRNUM, 5) = 'CREDT' THEN 'Credit Memo'
WHEN LEFT(OAKVA..GL20000.ORCTRNUM, 5) = 'DEBIT' THEN 'Debit Memo'
ELSE OAKVA..GL20000.SOURCDOC END as [DocumentType],
COALESCE(OAKVA..RM20101.SLSAMNT, 0) as [Sales],
COALESCE(OAKVA..RM20101.TAXAMNT, 0) as [Tax],
COALESCE(OAKVA..RM20101.ORTRXAMT, 0) as [Total1],
OAKVA..GL20000.ORTRXSRC as [Source],
OAKVA..GL00105.ACTNUMST as [PostingAccount],
RIGHT(rtrim(OAKVA..GL00105.ACTNUMST), 6) as [NaturalAccount],
OAKVA..GL00100.ACTDESCR as [Description],
OAKVA..ME50405.USERDEF1 as [PropertyManagement],
OAKVA..GL20000.DEBITAMT as [Debit],
OAKVA..GL20000.CRDTAMNT as [Credit],
OAKVA..GL20000.CRDTAMNT - OAKVA..GL20000.DEBITAMT as [Total2]
from OAKVA..GL20000  
INNER JOIN OAKVA..RM20101 on OAKVA..GL20000.ORCTRNUM = OAKVA..RM20101.DOCNUMBR
INNER JOIN OAKVA..RM20201 on OAKVA..RM20201.APTODCNM = OAKVA..RM20101.DOCNUMBR
INNER JOIN OAKVA..SOP30200 on OAKVA..RM20201.APTODCNM = OAKVA..SOP30200.SOPNUMBE
INNER JOIN OAKVA..RM00101 on OAKVA..RM00101.CUSTNMBR = OAKVA..RM20101.CUSTNMBR
INNER JOIN OAKVA..GL00105 on OAKVA..GL20000.ACTINDX = OAKVA..GL00105.ACTINDX
INNER JOIN OAKVA..GL00100 on OAKVA..GL00105.ACTINDX = OAKVA..GL00100.ACTINDX
INNER JOIN OAKVA..ME50411 on OAKVA..SOP30200.SOPNUMBE = OAKVA..ME50411.DOCNUMBR
INNER JOIN OAKVA..ME50405 on OAKVA..ME50411.ME_Contract_Number = OAKVA..ME50405.ME_Contract_Number
where OAKVA..RM20101.RMDTYPAL = '3'
and SUBSTRING(OAKVA..GL00105.ACTNUMST, CHARINDEX('-', ACTNUMST) + 1, 1) = '4'
and EXISTS (select i.aptodcnm,d.aptodcnm
from rm20201 as i
inner join rm20201 as d
 on i.apfrdcnm=d.apfrdcnm
where i.aptodcty=1
and d.aptodcty = 3)
and OAKVA..GL20000.TRXDATE BETWEEN '2011-08-01 00:00:00.000' and '2011-08-31 00:00:00.000'
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 26

Expert Comment

by:Shaun Kline
ID: 36817215
The script I provided would be for use in a stored procedure. Removing the specific payment number would all the script to be used in a view or straight SQL. The addition of aptodcty=1 was missed when I first posted.

The script was specific to bringing back the invoice value where another record had the same payment number. If you want to pull back both the invoice and the debit memo record, then you need to perform a self-join as Lowfatspread indicated above. It all depends on what you are trying to return from you SQL.
SELECT APTODCNM FROM RM20201 WHERE APFRDCNM = <Payment #> AND EXISTS (SELECT 1 FROM RM20201 as A WHERE APFRDCNM = RM20201.APFRDCNM and APTODCTY <> 1) and APTODCTY = 1

Open in new window

0
 
LVL 1

Author Comment

by:apitech
ID: 36817230
Guys:

If it helps, I have attached a screenshot of what I am trying to do.  Please offer some direction.  I'm on a time crunch, on this.

Thanks!

Apitech
Easy-Peesy.docx
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36817315
i didn't say anything about exists...

more like this


select 
CASE WHEN OAKVA..GL20000.SOURCDOC IN ('PMTRX', 'GJ') 
     THEN CONVERT(VARCHAR(20), OAKVA..GL20000.JRNENTRY)
     WHEN OAKVA..GL20000.SOURCDOC = 'SJ' 
     then OAKVA..GL20000.ORCTRNUM 
     ELSE '' 
     END as [DocumentNumber], 
I.APTODCNM as [InvoiceNumber],
OAKVA..RM20101.DOCDATE as [DocumentDate], 
OAKVA..GL20000.TRXDATE as [PostDate], 
LEFT(OAKVA..RM20101.CUSTNMBR, 3) as [ClientCode], 
OAKVA..RM20101.CUSTNMBR as [ClientNumber], 
OAKVA..RM00101.CUSTNAME as [ClientName],
CASE LEFT(OAKVA..GL20000.ORCTRNUM, 5)
     WHEN 'CREDT' THEN 'Credit Memo' 
     WHEN 'DEBIT' THEN 'Debit Memo' 
     ELSE OAKVA..GL20000.SOURCDOC 
     END as [DocumentType],
COALESCE(OAKVA..RM20101.SLSAMNT, 0) as [Sales], 
COALESCE(OAKVA..RM20101.TAXAMNT, 0) as [Tax], 
COALESCE(OAKVA..RM20101.ORTRXAMT, 0) as [Total1], 
OAKVA..GL20000.ORTRXSRC as [Source], 
OAKVA..GL00105.ACTNUMST as [PostingAccount], 
RIGHT(rtrim(OAKVA..GL00105.ACTNUMST), 6) as [NaturalAccount], 
OAKVA..GL00100.ACTDESCR as [Description], 
OAKVA..ME50405.USERDEF1 as [PropertyManagement], 
OAKVA..GL20000.DEBITAMT as [Debit], 
OAKVA..GL20000.CRDTAMNT as [Credit], 
OAKVA..GL20000.CRDTAMNT - OAKVA..GL20000.DEBITAMT as [Total2]
from OAKVA..GL20000  
INNER JOIN OAKVA..RM20101 on OAKVA..GL20000.ORCTRNUM = OAKVA..RM20101.DOCNUMBR
INNER JOIN (select * from OAKVA..RM20201 
              where aptodcty = 3
           ) as d   
   on D.APTODCNM = OAKVA..RM20101.DOCNUMBR
Left Outer JOIN (select * from OAKVA..RM20201 
                  where aptodcty = 1
                ) as i   
   on i.apfrdcnm=d.apfrdcnm
INNER JOIN OAKVA..SOP30200 on D.APTODCNM = OAKVA..SOP30200.SOPNUMBE
INNER JOIN OAKVA..RM00101 on OAKVA..RM00101.CUSTNMBR = OAKVA..RM20101.CUSTNMBR
INNER JOIN OAKVA..GL00105 on OAKVA..GL20000.ACTINDX = OAKVA..GL00105.ACTINDX
INNER JOIN OAKVA..GL00100 on OAKVA..GL00105.ACTINDX = OAKVA..GL00100.ACTINDX
INNER JOIN OAKVA..ME50411 on OAKVA..SOP30200.SOPNUMBE = OAKVA..ME50411.DOCNUMBR
INNER JOIN OAKVA..ME50405 on OAKVA..ME50411.ME_Contract_Number = OAKVA..ME50405.ME_Contract_Number 
where OAKVA..RM20101.RMDTYPAL = '3'
and SUBSTRING(OAKVA..GL00105.ACTNUMST, CHARINDEX('-', ACTNUMST) + 1, 1) = '4'
and OAKVA..GL20000.TRXDATE BETWEEN '2011-08-01 00:00:00.000' 
-- missing last day ?  time component should be '23:59:59.997' 00:00 is start of day
                               and '2011-08-31 00:00:00.000'

Open in new window

0
 
LVL 1

Author Comment

by:apitech
ID: 36817502
No results were returned.  I should have had two records (debit memos) returned.
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 36817632
ok had the pm joined to the debit rather than the invoice..

try this
select 
CASE WHEN OAKVA..GL20000.SOURCDOC IN ('PMTRX', 'GJ') 
     THEN CONVERT(VARCHAR(20), OAKVA..GL20000.JRNENTRY)
     WHEN OAKVA..GL20000.SOURCDOC = 'SJ' 
     then OAKVA..GL20000.ORCTRNUM 
     ELSE '' 
     END as [DocumentNumber], 
I.APTODCNM as [InvoiceNumber],
OAKVA..RM20101.DOCDATE as [DocumentDate], 
OAKVA..GL20000.TRXDATE as [PostDate], 
LEFT(OAKVA..RM20101.CUSTNMBR, 3) as [ClientCode], 
OAKVA..RM20101.CUSTNMBR as [ClientNumber], 
OAKVA..RM00101.CUSTNAME as [ClientName],
CASE LEFT(OAKVA..GL20000.ORCTRNUM, 5)
     WHEN 'CREDT' THEN 'Credit Memo' 
     WHEN 'DEBIT' THEN 'Debit Memo' 
     ELSE OAKVA..GL20000.SOURCDOC 
     END as [DocumentType],
COALESCE(OAKVA..RM20101.SLSAMNT, 0) as [Sales], 
COALESCE(OAKVA..RM20101.TAXAMNT, 0) as [Tax], 
COALESCE(OAKVA..RM20101.ORTRXAMT, 0) as [Total1], 
OAKVA..GL20000.ORTRXSRC as [Source], 
OAKVA..GL00105.ACTNUMST as [PostingAccount], 
RIGHT(rtrim(OAKVA..GL00105.ACTNUMST), 6) as [NaturalAccount], 
OAKVA..GL00100.ACTDESCR as [Description], 
OAKVA..ME50405.USERDEF1 as [PropertyManagement], 
OAKVA..GL20000.DEBITAMT as [Debit], 
OAKVA..GL20000.CRDTAMNT as [Credit], 
OAKVA..GL20000.CRDTAMNT - OAKVA..GL20000.DEBITAMT as [Total2]
from OAKVA..GL20000  
INNER JOIN OAKVA..RM20101 on OAKVA..GL20000.ORCTRNUM = OAKVA..RM20101.DOCNUMBR
INNER JOIN (select * from OAKVA..RM20201 
              where aptodcty = 3
           ) as d   
   on D.APTODCNM = OAKVA..RM20101.DOCNUMBR
Left Outer JOIN (select * from OAKVA..RM20201 
                  where aptodcty = 1
                ) as i   
   on i.apfrdcnm=d.apfrdcnm
left outer JOIN OAKVA..SOP30200 on i.APTODCNM = OAKVA..SOP30200.SOPNUMBE
INNER JOIN OAKVA..RM00101 on OAKVA..RM00101.CUSTNMBR = OAKVA..RM20101.CUSTNMBR
INNER JOIN OAKVA..GL00105 on OAKVA..GL20000.ACTINDX = OAKVA..GL00105.ACTINDX
INNER JOIN OAKVA..GL00100 on OAKVA..GL00105.ACTINDX = OAKVA..GL00100.ACTINDX
INNER JOIN OAKVA..ME50411 on OAKVA..SOP30200.SOPNUMBE = OAKVA..ME50411.DOCNUMBR
INNER JOIN OAKVA..ME50405 on OAKVA..ME50411.ME_Contract_Number = OAKVA..ME50405.ME_Contract_Number 
where OAKVA..RM20101.RMDTYPAL = '3'
and SUBSTRING(OAKVA..GL00105.ACTNUMST, CHARINDEX('-', ACTNUMST) + 1, 1) = '4'
and OAKVA..GL20000.TRXDATE BETWEEN '2011-08-01 00:00:00.000' 
-- missing last day ?  time component should be '23:59:59.997' 00:00 is start of day
                               and '2011-08-31 00:00:00.000'

Open in new window

0
 
LVL 1

Author Comment

by:apitech
ID: 36817763
That was it!!!!

Thank you so much, Lowfatspread!!!!!  You're awesome!

John
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 36817870
ok not sure if the left joins are necessary or in the right places , but it dhouldn't make any difference to the actual results.

you need to gain a fuller understanding of what relational database processing is about and how you navigate the various types of relationship ...

it may help you to consider the rm20201 table as a union (set) of the three distinct datatypes debit,payment,invoice

so try accessing the table via subqueries specifying each individual type sp that you can join the data...
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MSSQL 2014 Query Synthax 8 38
SQL server 2008 SP4 29 34
SyBase Query Syntax Date Time conversion 4 11
Query 14 19
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now