Skip to Content
0
Former Member
May 20, 2016 at 01:25 PM

Query, joining tables and pulling OPCH NumAtCard/ORPC NumAtCard at the same time

1006 Views

I would like to basically extract the information from the outgoing payment rows. I need it to be able to read the invoices as well as credit memos that are paid in the outgoing payment. My question is how do I correct this query so that it pulls OPCH NumAtCard/ORPC NumAtCard in the same column and the doc total from both tables as well, instead of having 2 columns for each table. Is this possible? If not, how do I fix it so that it reads both OPCH NumAtCard/ORPC NumAtCard at the same time in different columns.

SELECT T0.[Canceled],T0.[TrsfrAcct],T0.[DocNum] 'Outgoing Payment No.',T0.[DocDate] As 'Outgoing Payment Date', T0.[CardCode] As 'Customer No.', T0.[CardName] As 'Customer Name',T2.[NumAtCard] As 'Invoice No.', T2.[DocTotal] As 'Invoice Total',

T3.[NumAtCard] As 'Credit Memo No.',T3.[DocTotal] As 'Credit Memo Total', T1.[SumApplied] As 'Invoice Amt. Paid', T0.[TrsfrSum] As 'Outgoing Pmt. Total', T0.[TrsfrRef] As 'Amex Ref. No.',

CASE

T0.[TrsfrAcct] WHEN '_SYS00000000283' THEN 'American Express 2836'

END[Credit Card]

FROM [dbo].[OVPM] T0

INNER JOIN [dbo].[VPM2] T1 ON T0.DocNum = T1.DocNum

INNER JOIN OPCH T2 ON T1.DocEntry = T2.DocEntry

INNER JOIN ORPC T3 ON T1.DocEntry = T3.DocEntry

WHERE T0.[DocDate] >=[%0] and T0.[DocDate] <=[%1] AND T0.[TrsfrAcct]='_SYS00000000283' AND T0.[Canceled]='N'

ORDER BY T0.[DocDate],T0.[CardName], T0.[TrsfrSum]