I am writing SQL to report data similar to the below format. The row's beginning with 'P' are summary rows for a specific vendor for the corresponding remittance 'R' rows below it associated to the payment or that vendor. For example the PAID_AMT amount on the P rows is a summed value (1000.50 = sum of corresponding R rows 110 + 800 + 100.50 = 1000.50) for 'NW Forest Supplies'. I started doing this as a UNION query however the challenge is that the 'P' rows represent different fields from what the 'R' rows represent, so combining the data together is what I am struggling with.
The final row beginning with 'C' is a overall summary of all rows and I am also struggling with and is not correct. Per the example, it should sum the total PAID_AMT values from the 'P' rows which would be 2056.00 (1000.50 + 55 + 1000.50), then output the total count of the number of payments (3), and then final the total count of remittances - 7 in this example.
Perhaps it's better to make use of a CTE and union in the final result, or make use of temp tables to insert the data for the 'P' and 'R' data into their own tables, and then query from?
Example of how report output needs to be:
SELECT
'P'
, 'CRD'
, 'PayerName.Payables'
, A.ADDRESS4
, A.PYMNT_ID_REF
, SUM(B.PAID_AMT)
, A.REMIT_VENDOR
, CAST(B.REMIT_ADDR_SEQ_NUM AS VARCHAR)
, A.NAME1
, 'USD'
FROM
PS_PAYMENT_TBL A
INNER JOIN PS_PYMNT_VCHR_XREF B
ON B.PYMNT_ID = A.PYMNT_ID
AND A.BANK_SETID = B.BANK_SETID
AND A.BANK_CD = B.BANK_CD
AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY
WHERE A.PYMNT_DT > '01/12/2021'
GROUP BY
A.REMIT_VENDOR
, A.ADDRESS4
, A.PYMNT_ID_REF
, A.REMIT_VENDOR
, CAST(B.REMIT_ADDR_SEQ_NUM AS VARCHAR)
, A.NAME1
UNION
SELECT
'R'
, A.PYMNT_ID_REF
, C.INVOICE_ID
, C.INVOICE_DT
, C.GROSS_AMT
, C.DSCNT_AMT
, B.VOUCHER_ID
, C.PO_ID
, ''
, ''
FROM
PS_PYMNT_VCHR_XREF B
INNER JOIN PS_PAYMENT_TBL A
ON B.PYMNT_ID = A.PYMNT_ID
AND A.BANK_SETID = B.BANK_SETID
AND A.BANK_CD = B.BANK_CD
AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY
INNER JOIN PS_VOUCHER C
ON B.VOUCHER_ID = C.VOUCHER_ID
AND C.BUSINESS_UNIT = B.BUSINESS_UNIT
WHERE A.PYMNT_DT > '01/12/2021'
UNION
SELECT
'C'
, SUM(A.PAID_AMT)
, COUNT(A.PAID_AMT) , COUNT(??)
FROM
PS_PYMNT_VCHR_XREF A
INNER JOIN PS_PAYMENT_TBL B
ON B.PYMNT_ID = A.PYMNT_ID
AND A.BANK_SETID = B.BANK_SETID
AND A.BANK_CD = B.BANK_CD
AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY
WHERE A.PYMNT_DT > '01/12/2021'
GROUP BY B.NAME1
ORDER BY 7, 1
Edit 1/17/21:
I modified the SQL slightly, and believe I have the individual parts of the code working, but I am struggling as to how I can combine these the same way the example has it illustrated, with having the Remittances displayed below each Payment.
The UNION was the only function I could think of to combine the data vertically, but I need to be able to group the remittances below each corresponding payment row and I don't know what ways I can do this. Also it's worth mentioning that the columns for the Payment and Remittances queries are different (number of columns and type) and do not always relate to each other.
--Payment:
SELECT B.ADDRESS4, B.PYMNT_ID_REF, SUM(A.PAID_AMT), B.REMIT_VENDOR, A.REMIT_ADDR_SEQ_NUM,
B.NAME1
FROM PS_PYMNT_VCHR_XREF A
INNER JOIN PS_PAYMENT_TBL B ON B.PYMNT_ID = A.PYMNT_ID AND A.BANK_SETID = B.BANK_SETID
AND A.BANK_CD = B.BANK_CD
AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY
WHERE B.PYMNT_DT > '2021-01-03'
--AND A.PYMNT_ID = '0000263556'
GROUP BY B.ADDRESS4, B.PYMNT_ID_REF, B.REMIT_VENDOR, A.REMIT_ADDR_SEQ_NUM,
B.NAME1
--Remittances:
SELECT 2, A.PYMNT_ID_REF, C.INVOICE_ID, C.INVOICE_DT, C.GROSS_AMT, C.DSCNT_AMT, B.VOUCHER_ID,
C.PO_ID
FROM PS_PYMNT_VCHR_XREF B
INNER JOIN PS_PAYMENT_TBL A ON B.PYMNT_ID = A.PYMNT_ID AND A.BANK_SETID = B.BANK_SETID
AND A.BANK_CD = B.BANK_CD
AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY
INNER JOIN PS_VOUCHER C ON B.VOUCHER_ID = C.VOUCHER_ID AND C.BUSINESS_UNIT = B.BUSINESS_UNIT
WHERE A.PYMNT_DT > '2021-01-03'
--AND A.PYMNT_ID = '0000263556'
--Control data:
WITH CONTROLTOTALS AS (
SELECT SUM(A.PAID_AMT) AS TOT_PAID_AMT
FROM PS_PYMNT_VCHR_XREF A
INNER JOIN PS_PAYMENT_TBL B ON B.PYMNT_ID = A.PYMNT_ID AND A.BANK_SETID = B.BANK_SETID
AND A.BANK_CD = B.BANK_CD
AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY
WHERE B.PYMNT_DT > '2021-01-03' )
, CONTROLCOUNT1 AS (
SELECT COUNT(PYMNT_ID_REF) AS PYMNT_COUNT
FROM PS_PAYMENT_TBL B
WHERE B.PYMNT_DT > '2021-01-03'
AND PYMNT_ID <> '' ) ,
CONTROLCOUNT2 AS (
SELECT COUNT(*) AS REMIT_COUNT
FROM PS_PYMNT_VCHR_XREF A
INNER JOIN PS_PAYMENT_TBL B ON B.PYMNT_ID = A.PYMNT_ID AND A.BANK_SETID = B.BANK_SETID
AND A.BANK_CD = B.BANK_CD
AND A.BANK_ACCT_KEY = B.BANK_ACCT_KEY
WHERE PYMNT_DT > '2021-01-03' )
SELECT 'C', A.TOT_PAID_AMT, B.PYMNT_COUNT, C.REMIT_COUNT
FROM CONTROLTOTALS A, CONTROLCOUNT1 B, CONTROLCOUNT2 C
EDIT 1/18/21:
Here is the table structure for the 3 tables being used in these queries:
Here is some sample data (2 payment rows for two vendors (Radiometer America - payment ID 023946, and Highmark Inc. - Payment ID 023943), along with corresponding remittances rows associated with each vendor:
Here is sample Remittance data that is associate to each payment for a vendor (Highmark is highlighted in Red with 2 Remittances, Radiometer America is in Green with 6 remittances, for a total of 8 for the example output):
Here is the final Control (Grand) totals row at the bottom. The TOT_PAID_AMT represents the total 2 payments from above for each vendor (8901.94 + 997,343.44), the total payment count (2), and the total remittances in the report (2 from Highmark, 6 from Radiometer America:
So now I need to bring all this together and output it the same way the original example displays it as, with the remittances displayed below each vendor payment.
This is how the output would look like for this particular example, notice the remittances are displayed with the corresponding payment:
So I have all the individual pieces, but I now need to tie them together in this format, for which I am struggling with. Hope this provides more clarity.
Table structure / sample files here:
