Solved

SQL Combine group script with single lines

Posted on 2013-01-24
3
402 Views
Last Modified: 2013-01-27
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
Comment
Question by:rwheeler23
3 Comments
 
LVL 39

Accepted Solution

by:
appari earned 400 total points
ID: 38817368
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
 

Author Comment

by:rwheeler23
ID: 38818355
I am getting this message.

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'Check'.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 38823392
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

809 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