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 different then I end up pulling more than the most recent order (older orders for the same part at different prices, or with different descriptions). Is there any way to make sql only pull the last order but still have the description and price that I need to show? Below is the Query and the results below.
select
–Max(a.OrderNum) as OrderNum,
Max(a.OrderDate) as OrderDate,
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.OrderNum
inner join erp.PartBin c
on b.Company =c.Company
and b.PartNum = c.PartNum
left outer join erp.Customer d
on a.Company = d.Company
and a.CustNum = d.CustNum
where a.Company = ‘PPI’ and b.UnitPrice > ‘0’ and a.CustNum = ‘224’ --and a.OrderDate >= Dateadd(day,-360,GetDate())
group by
a.CustNum,
b.PartNum,
–a.OrderDate,
b.LineDesc,
a.Company,
b.UnitPrice,
c.OnhandQty
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.OrderNum
inner join erp.PartBin c
on b.Company =c.Company
and b.PartNum = c.PartNum
left outer join erp.Customer d
on a.Company = d.Company
and a.CustNum = d.CustNum
where a.Company = ‘XXX’ and b.UnitPrice > ‘0’ and b.partnum = ‘PART’
group by a.CustNum,b.PartNum,b.LineDesc,a.Company,b.UnitPrice,c.OnhandQty, a.orderdate order by row_number() over(partition by a.custnum order by a.orderdate desc);
I was able to return the top/most recent row for each customer by part.
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) 'MaxOrderDate' FROM Erp.OrderDtl GROUP BY PartNum) o ON p.PartNum=o.PartNum
This is simply getting everything in set p (PartBin) and joining it to the highest Order Date per PartNumber from the o set. You can get a lot fancier and add in where clauses to only select specific orders, or filter the PartBin table to only select specific Parts or Warehouses.
@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.
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.OrderNum
inner join erp.PartBin c
on b.Company =c.Company
and b.PartNum = c.PartNum
left outer join erp.Customer d
on a.Company = d.Company
and a.CustNum = d.CustNum
where a.Company = ‘PPI’ and b.UnitPrice > ‘0’ and a.CustNum = ‘224’–and b.partnum = ‘PART’
group by a.CustNum,b.PartNum,b.LineDesc,a.Company,b.UnitPrice,c.OnhandQty, a.orderdate
order by row_number() over(partition by b.partnum order by a.orderdate desc)
