My query is suppose to have the results sum by vendorID, however,
I'm obviously doing saoomethng wrong.
The PM30200 is the transaction table and the PM00200 is the Vendor Master table. I only need the PM00200 table for the Vendor Name.
Here is my query and below is a sample of the results:
--
SELECT top 25 b.vendorid, b.vendname,sum(a.docamnt) as [Spend]
FROM PM30200 a
inner join pm00200 b
on a.vendorid = b.vendorid
WHERE a.DOCDATE BETWEEN '2010-06-01 00:00:00.000' AND '2011-05-31 00:00:00.000'
and a.doctype = '6'
and substring(a.pordnmbr,2,1) <> 'c'
group by b.vendorid, b.vendname,a.docamnt
order by a.docamnt desc
--
VendorID Vendor Name Spend
010101 abc, inc 100.00
010101 abc, inc 20.00
454545 USA Co 400.00
010101 abc, inc 125.00
Results are summing by trx, not summing in total by vendorID.
Appreciate your assistance.
Thanks,
Andrew
I'm obviously doing saoomethng wrong.
The PM30200 is the transaction table and the PM00200 is the Vendor Master table. I only need the PM00200 table for the Vendor Name.
Here is my query and below is a sample of the results:
--
SELECT top 25 b.vendorid, b.vendname,sum(a.docamnt) as [Spend]
FROM PM30200 a
inner join pm00200 b
on a.vendorid = b.vendorid
WHERE a.DOCDATE BETWEEN '2010-06-01 00:00:00.000' AND '2011-05-31 00:00:00.000'
and a.doctype = '6'
and substring(a.pordnmbr,2,1) <> 'c'
group by b.vendorid, b.vendname,a.docamnt
order by a.docamnt desc
--
VendorID Vendor Name Spend
010101 abc, inc 100.00
010101 abc, inc 20.00
454545 USA Co 400.00
010101 abc, inc 125.00
Results are summing by trx, not summing in total by vendorID.
Appreciate your assistance.
Thanks,
Andrew