Hey guys I’m having some trouble narrowing down my query results in SQL. My goal is look at the parts currently in inventory and pull the last most recent order for any customer that was made for any of the parts in inventory. My current problem is that if the price or the line description is differ…

@TinaK I was able to take your script and modify the over condition to make it work by part. Looks like its working now. thanks for the help. Any chance you could explain what the " order by row_number() over(partition by b.partnum order by a.orderdate desc)" is doing? Below is what I did. selec…

Here’s what I added to make it work: select top(1) with ties Max(a.OrderDate) as OrderDate, Max(a.OrderNum) as OrderNum, a.CustNum, a.Company, b.PartNum, b.LineDesc, b.UnitPrice, c.OnhandQty from erp.OrderHed a left outer join erp.OrderDtl b on a.Company = b.Company and a.OrderNum = b.O…

I would recommend starting with a simple subquery to fetch what you are looking for. Your query will look something like this: SELECT * FROM Erp.Partbin p --Get all parts on hand LEFT JOIN --Keep all parts on hand but add the highest order date if found (SELECT PartNum,MAX(OrderDate) 'MaxOrderDat…

That’s a Windowing Function, and they are super helpful in the right instances. [image] SQL Window Functions | Advanced SQL - Mode This lesson of the SQL tutorial for data analysis covers SQL windowing functions such as ROW_NUMBER(), NTILE, LAG, and LEAD.

@Banderson Thanks for that. It’s going to take me some time and practice to really understand this.

Query Problem in SQL

Epicor ERP 10

Banderson (Brandon Anderson) June 16, 2022, 5:58pm 5

That’s a Windowing Function, and they are super helpful in the right instances.

BAQ Question for keeping results separate

Topic		Replies	Views
Last or First Epicor ERP 10 baq	11	1889	December 26, 2018
Last Quoted, Last Quote price, Last Quote number Epicor ERP 10 baq , aggregate	5	914	November 29, 2018
BAQ return only 1 row per part number Epicor ERP 10	20	9216	April 3, 2024
BAQ - Get recent date of part purchased and quantity Epicor ERP 10 e10 , baq	10	1496	May 2, 2024
BAQ How to get the Sales Order with Oldest Date Epicor ERP 10 epicor , baq	15	4585	July 8, 2020

Query Problem in SQL

Related topics