SQL code to show top X percent of vendor data

I had an interesting request the other day – how do you show the top 10 (or 20) percent of vendor payments (or invoices) for a particular date range?

This is something that has to be done in a several steps – first you have to get your data, then summarize of it, then calculate the top X percent of the summary results.

Since the request was to vary the percentage and also the type of data looked at (invoices or payments), I would recommend making both of those parameters.

Below is sample code to make this work for Dynamics GP data. You can wrap this up in a stored procedure and add whatever other fields are needed to be able to use it in a report.

In my example I set the date parameters to be a pretty narrow range, to ensure the data returns quickly, but this will work for whatever date range you want to use and crossing years is no problem. You can also set the @top_pct parameter to be 100 to see all of the summary data. The @doc_type parameter can be either ‘invoices’ or ‘payments’.

declare @from datetime = '1/1/2016'
declare @to datetime = '2/29/2016'
declare @top_pct int = 10
declare @doc_type char(10) = 'invoices' -- or 'payments'

select top (@top_pct) percent
s.VENDORID Vendor_ID,
v.VENDNAME Vendor_Name,
s.Total

from
(select d.VENDORID, sum(d.DOCAMNT) Total
from
(select VENDORID, DOCAMNT
from PM20000
where DOCDATE between @from and @to
and VOIDED = 0
and DOCTYPE = (case @doc_type
when 'invoices' then 1
when 'payments' then 6 end)

union all

select VENDORID, DOCAMNT
from PM30200
where DOCDATE between @from and @to
and VOIDED = 0
and DOCTYPE = (case @doc_type
when 'invoices' then 1
when 'payments' then 6 end)
) d --data
group by d.VENDORID) s --summary

left outer join PM00200 v --vendor master
on s.VENDORID = v.VENDORID

order by s.Total desc