3d small people with a motherboard. 3d image. Isolated white background.

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

Trackbacks/Pingbacks

  1. SQL code to show top X percent of vendor data – Mar 21, Victoria Yudin - March 21, 2016

    […] Continued on Source: SQL code to show top X percent of vendor data | Victoria Yudin […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: