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?
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.
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
I think your ‘and’ for the 2017/2019 is what is screwing you up. I think that should be ‘or’
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
I replaced the “And” with “Or” and got the same results. I will try the Min Calculations. Thanks for your quick reply.
The Min calculations are not working either. Any other suggestions?
You are going to have to be more descriptive . “Not working” doesn’t tell me enough.
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
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
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
That worked! Thank you for your help.
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.
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.
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!