New Vendor listing

sql

(Scott Swank) #1

Hello everyone. I have been tasked with getting a list of Vendors (Suppliers) that have been entered into Epicor in 2018. I noticed that there is not a field for Entered Date, so my finance team came up with a solution to query Suppliers paid only in 2018. I am having trouble coming up with a query to show this data. Can you assist me?


(Brandon Anderson) #2

Should be the vendor table , tied to the AP header table, put a criteria on the invoice date.

Throw up some screen shots of what you have now and what you’re having trouble with.


(Scott Swank) #3

Here is the query so far…My finance team has stated that the results are incorrect. There are some that were paid in 2017 from this result set.

select v.VendorID
,v.Name
from erp.vendor v
left outer join erp.APInvHed ap on ap.Company = v.Company and ap.VendorNum = v.VendorNum
where ap.invoicedate between ‘01/01/2018’ and ‘12/31/2018’ and not exists (select ap.InvoiceDate where (ap.InvoiceDate between ‘01/01/2017’ and ‘12/31/2017’) and (ap.InvoiceDate between ‘01/01/2019’ and ‘01/11/2019’))
group by v.VendorID, v.name


(Brandon Anderson) #4

I think your ‘and’ for the 2017/2019 is what is screwing you up. I think that should be ‘or’


(Brandon Anderson) #5

I might be easier to do a Min() calculation on invoice date and take the ones where the min is greater than 1-1-2018 and less the 12-31-2018


(Scott Swank) #6

I replaced the “And” with “Or” and got the same results. I will try the Min Calculations. Thanks for your quick reply.


(Scott Swank) #7

The Min calculations are not working either. Any other suggestions?


(Brandon Anderson) #8

You are going to have to be more descriptive . “Not working” doesn’t tell me enough.


(Al) #9

i do not know if this will satisfy your finance requirement, but SubShipH table has got Ship date for sub./con. processes which usually used for outsource process suppliers, and for purchased part PartTran have the sysdate when parts been received from purchasing to stock, inspection, and/or to jobs…both tables have Vendor Num and system date


(Scott Swank) #10

How will this allow me to exclude the Vendors if they were shipped before 2018 and after 2018? The query that I created will pull data that includes ap.invoicedate in 2018, but when I verify the vendor invoices, there are some with 2017. I need it to exclude any vendor that was paid in 2017 or 2019 and bring back vendors that were paid in 2018 only.
Here is the latest query with Min:
select v.VendorID
,v.Name
,v.VendorNum
,min(ap.invoicedate)
from erp.vendor v
left outer join erp.APInvHed ap on ap.Company = v.Company and ap.VendorNum = v.VendorNum
where ap.invoicedate between ‘01/01/2018’ and ‘12/31/2018’ and not exists (select ap.InvoiceDate where (ap.InvoiceDate between ‘01/01/2015’ and ‘12/31/2017’) and (ap.InvoiceDate between ‘01/01/2019’ and ‘01/11/2019’))
group by v.VendorID
,v.Name
,v.VendorNum


(Kenneth Hayes) #11

Try this SQL Query -

Select
	a.Company,
	VendorID,
	Name,
	Address1,
	Address2,
	City,
	State,
	ZIP,
	Country,
	a.TermsCode,
	IsNull(PaymentAmt, 0) Payments2018,
	a.Print1099
From
	Epicor10.dbo.Vendor a
Left Join (
		   Select
			Company,
			VendorNum,
			Year(CheckDate) PaymentYear,
			Sum(DocCheckAmt) PaymentAmt
		   From
			Epicor10.Erp.CheckHed
		   Where
			 Voided = 0
		   Group By
			Company,
			VendorNum,
			Year(CheckDate)
		  ) b
On	a.Company = b.Company
	And a.VendorNum = b.VendorNum
	And b.PaymentYear = 2018

(Scott Swank) #12

That worked! Thank you for your help.


(Scott Swank) #13

This query seems to be summing the total paid in 2018, but it does not filter out if they were paid in 2017. I am not sure if I stated that correctly earlier, but I need to eliminate rows if they were paid in 2017 or 2019.


(Brandon Anderson) #14

I was wondering what Kenny was getting at.

For the one that you calculated the min, you left that and statement in there, so it’s only going to exclude if they are in 2017 and 2019. Just get rid of that altogether, you will get a list of all of your vendors and the first date that they got an invoice. Then drop the list into excel, chop off before and after 2018.


(Scott Swank) #15

Thanks Banderson. After reading your post more carefully, I decided to take off the where clause altogether and copy the results to Excel and filter the min date to only 2018. Thanks for helping!