• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

SQL Combine group script with single lines

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
FROM PM30300
WHERE VENDORID='SAMPLE'
GROUP BY APFRDCNM,DATE1,VENDORID) T

--SCRIPT 2
SELECT VENDORID as Vendor_ID,APFRDCNM,APTODCNM as Check_Number,DATE1 as Check_Date,APFRMAPLYAMT AS Check_Amount FROM PM30300
WHERE VENDORID='SAMPLE'

-- 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

--DESIRED OUTPUT
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
0
rwheeler23
Asked:
rwheeler23
2 Solutions
 
appariCommented:
try this
SELECT
Check_Number, Check_Amount , Invoice.DATE1 as Check_Date,
APTODCNM , APFRMAPLYAMT
FROM (Select * from PM30300  WHERE VENDORID='SAMPLE') Invoice
Join (
SELECT VENDORID as Vendor_ID,APFRDCNM as Check_Number,DATE1 as Check_Date,SUM(APFRMAPLYAMT) AS Check_Amount
FROM PM30300
WHERE VENDORID='SAMPLE'
GROUP BY APFRDCNM,DATE1,VENDORID) Check
on Invoice.VendorID = Check.Vendor_ID
and Invoice.APFRDCNM = Check.Check_Number

Open in new window

0
 
rwheeler23Author Commented:
I am getting this message.

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'Check'.
0
 
Anthony PerkinsCommented:
Since "Check" is a reserved keyword you either need to use a different alias or enclose Check in square brackets as in [Check]
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now