SQL Combine group script with single lines
Posted on 2013-01-24
How do I combine these two scripts?
I have a table called PM30300 that contains records for all paid transactions. There is a field called APFRDCNM which will be the check number and a field called APTODCNM which will be the invoice number.
It is possible that one check could pay mulitple invoices. So first I group by the check number to get the total of the check. Then I want to get all the invoices to which this check was applied.
I have included sample data. In the first example, CHK-1 was for $500 and paid the first two invoices.
-- SCRIPT 1
SELECT Vendor_ID,Check_Number,Check_Date,Check_Amount from (
SELECT VENDORID as Vendor_ID,APFRDCNM as Check_Number,DATE1 as Check_Date,SUM(APFRMAPLYAMT) AS Check_Amount
GROUP BY APFRDCNM,DATE1,VENDORID) T
SELECT VENDORID as Vendor_ID,APFRDCNM,APTODCNM as Check_Number,DATE1 as Check_Date,APFRMAPLYAMT AS Check_Amount FROM PM30300
-- SAMPLE DATA
VendorID APFRDCNM Check_Number Check_Date Check_Amount
SAMPLE CHK-1 TEST-1 2017-04-12 00:00:00.000 200.00000
SAMPLE CHK-1 TEST-2 2017-04-12 00:00:00.000 300.00000
SAMPLE CHK-2 TEST-3 2017-04-12 00:00:00.000 500.00000
SAMPLE CHK-2 TEST-4 2017-04-12 00:00:00.000 750.00000
So the goal is to have this dataset returned.
Check_Number Check_Amount Check_Date,Invoice_Number,Invoice Amount
CHK-1 $500.00 04/12/2017 TEST-1 $200
CHK-1 $500.00 04/12/2017 TEST-2 $300
CHK-2 $1250.00 04/12/2017 TEST-3 $500
CHK-2 $1250.00 04/12/2017 TEST-4 $750