Good morning -
I am working on a Payables query where I calculate the past 12 months of Invoices, Invoice Dollars, Payments and Payments Dollars.
This is what I got which returns two rows per Vendor ID - I would like it to be only one row.
I know what my issue is - since I am summing up Doc Types - SQL requires to group by Doc Types and since I am working two Doc Types (1 and 6).
What's the easiest solution?
Here's what I got:
SELECT
P.VENDORID VendorID,
---V.VENDNAME VendorName,
---V.ADDRESS1 Address1,
---V.ADDRESS2 Address2,
---V.City City,
V.STATE State,
V.ZIPCODE Zip,
V.COUNTRY Country,
V.TXIDNMBR as EIN,
V.PYMTRMID PaymentTerms,
case when P.DOCTYPE ='1'
then sum(P.DOCAMNT) else 0 end InvDollars,
case when P.DOCTYPE ='1'
then count(P.DOCNUMBR) else 0 end NumInvoices,
case when P.DOCTYPE ='6'
then sum(P.DOCAMNT) else 0 end PaymentDollars,
case when P.DOCTYPE ='6'
then count(P.DOCNUMBR) else 0 end NumPayments
from --all posted payables transactions, exclude voids
(select VENDORID, DOCTYPE, DOCDATE, PSTGDATE, DOCNUMBR,
DOCAMNT
from PM20000
where VOIDED = 0 and PSTGDATE >= DATEADD(MONTH, -12, GETDATE()) and DOCTYPE in ('1','6')
union all
select VENDORID, DOCTYPE, DOCDATE, PSTGDATE, DOCNUMBR,
DOCAMNT
from PM30200
where VOIDED = 0 and PSTGDATE >= DATEADD(MONTH, -12, GETDATE()) and DOCTYPE in ('1','6')) P
INNER JOIN -- vendor master
PM00200 V
ON V.VENDORID = P.VENDORID
where V.VNDCLSID not in ('REFUND','AC')
GROUP BY P.VENDORID, P.DOCTYPE, V.VENDNAME, V.ADDRESS1, V.ADDRESS2, V.CITY, V.STATE, V.ZIPCODE, V.TXIDNMBR,
V.COUNTRY, V.PYMTRMID
It returns two rows per Vendor -
