I have been trying to get the last value used for pricing. (Doc UnitPrice) I created a subquery called Last Price From OrderDtl using Company, CustNum, PartNum as joining fields for the top Query. LastUnitPrice Field Looks Like this:
Case when OrderDtl2.DocUnitPrice > 0 then Last_Value (OrderDtl2.DocUnitPrice) Over (Partition by OrderDtl2.PartNum order by OrderHed1.OrderDate RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) end
I never get the last or first value from the customer.
Could you copy and paste in the query phrase?
What is the Last Price based on? Ordered Date? By Customer as well? or just by Part?
Last Unit price of Order Date by customer and PartNum:
We accidentally lowered some prices on some customers and we just wanted the last price we charged them double check.
select distinct
[OrderDtl1].[Customer_CustID] as [Customer_CustID],
[OrderDtl1].[Customer_Name] as [Customer_Name],
[OrderDtl1].[OrderHed_SalesRepList] as [OrderHed_SalesRepList],
[PartCost1].[PartCost_PartNum] as [PartCost_PartNum],
[LastPrice].[Calculated_LastUnitPrice] as [Calculated_LastUnitPrice],
[OrderDtl1].[OrderDtl_LineDesc] as [OrderDtl_LineDesc],
(Sum(ShipDtl.SellingInventoryShipQty)) as [Calculated_ShipQty],
(Sum(OrderDtl1.OrderDtl_DocUnitPrice * ShipDtl.SellingInventoryShipQty)) as [Calculated_SalesAmt],
[PartCost1].[Calculated_STDBurden] as [Calculated_STDBurden],
[PartCost1].[Calculated_AVGCost] as [Calculated_AVGCost],
[PartCost1].[Calculated_LastCost] as [Calculated_LastCost]
from (select
[PartCost].[Company] as [PartCost_Company],
[PartCost].[PartNum] as [PartCost_PartNum],
(PartCost.StdBurdenCost + PartCost.StdLaborCost + PartCost.StdMaterialCost + PartCost.StdSubContCost + PartCost.StdMtlBurCost) as [Calculated_STDBurden],
(PartCost.AvgLaborCost + PartCost.AvgBurdenCost + PartCost.AvgMaterialCost + PartCost.AvgSubContCost + PartCost.AvgMtlBurCost) as [Calculated_AVGCost],
(PartCost.LastLaborCost + PartCost.LastBurdenCost + PartCost.LastMaterialCost + PartCost.LastSubContCost +PartCost.LastMtlBurCost) as [Calculated_LastCost]
from Erp.PartCost as PartCost) as PartCost1
right outer join (select
[OrderDtl].[Company] as [OrderDtl_Company],
[Customer].[CustID] as [Customer_CustID],
[OrderHed].[CustNum] as [OrderHed_CustNum],
[Customer].[Name] as [Customer_Name],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderHed].[SalesRepList] as [OrderHed_SalesRepList],
[OrderDtl].[LineType] as [OrderDtl_LineType],
[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
(avg(case when (OrderDtl.DocUnitPrice > 0) then OrderDtl.DocUnitPrice end)) as [Calculated_AVGUnitPrice],
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
[OrderDtl].[DocUnitPrice] as [OrderDtl_DocUnitPrice]
from Erp.OrderHed as OrderHed
inner join Erp.OrderDtl as OrderDtl on
OrderHed.Company = OrderDtl.Company
and OrderHed.OrderNum = OrderDtl.OrderNum
inner join Erp.Customer as Customer on
OrderDtl.Company = Customer.Company
and OrderDtl.CustNum = Customer.CustNum
group by [OrderDtl].[Company],
[Customer].[CustID],
[OrderHed].[CustNum],
[Customer].[Name],
[OrderDtl].[PartNum],
[OrderHed].[SalesRepList],
[OrderDtl].[LineType],
[OrderDtl].[LineDesc],
[OrderDtl].[OrderNum],
[OrderDtl].[OrderLine],
[OrderDtl].[DocUnitPrice]) as OrderDtl1 on
OrderDtl1.OrderDtl_Company = PartCost1.PartCost_Company
and OrderDtl1.OrderDtl_PartNum = PartCost1.PartCost_PartNum
left outer join Erp.ShipDtl as ShipDtl on
OrderDtl1.OrderDtl_Company = ShipDtl.Company
and OrderDtl1.OrderDtl_OrderNum = ShipDtl.OrderNum
and OrderDtl1.OrderDtl_OrderLine = ShipDtl.OrderLine
inner join Erp.ShipHead as ShipHead on
ShipDtl.Company = ShipHead.Company
and ShipDtl.PackNum = ShipHead.PackNum
inner join Erp.ShipTo as ShipTo on
ShipHead.Company = ShipTo.Company
and ShipHead.CustNum = ShipTo.CustNum
and ShipHead.ShipToNum = ShipTo.ShipToNum
left outer join (select
[OrderDtl2].[Company] as [OrderDtl2_Company],
[OrderDtl2].[PartNum] as [OrderDtl2_PartNum],
(case when OrderDtl2.DocUnitPrice > 0 then Last_Value (OrderDtl2.DocUnitPrice) Over (Partition by OrderDtl2.PartNum order by OrderHed1.OrderDate RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) end) as [Calculated_LastUnitPrice],
[OrderDtl2].[CustNum] as [OrderDtl2_CustNum]
from Erp.OrderDtl as OrderDtl2
inner join Erp.OrderHed as OrderHed1 on
OrderDtl2.Company = OrderHed1.Company
and OrderDtl2.OrderNum = OrderHed1.OrderNum) as LastPrice on
OrderDtl1.OrderDtl_Company = LastPrice.OrderDtl2_Company
and OrderDtl1.OrderDtl_PartNum = LastPrice.OrderDtl2_PartNum
and OrderDtl1.OrderHed_CustNum = LastPrice.OrderDtl2_CustNum
group by [OrderDtl1].[Customer_CustID],
[OrderDtl1].[Customer_Name],
[OrderDtl1].[OrderHed_SalesRepList],
[PartCost1].[PartCost_PartNum],
[LastPrice].[Calculated_LastUnitPrice],
[OrderDtl1].[OrderDtl_LineDesc],
[PartCost1].[Calculated_STDBurden],
[PartCost1].[Calculated_AVGCost],
[PartCost1].[Calculated_LastCost]
If you’re doing all this…
Why do you need it to be a subquery?
1 Like
The Top Query would not work with all that in it.
How about something like this? For your subquery.
select
-- [OrderDtl2].[Company] as [OrderDtl2_Company],
[OrderDtl2].[PartNum] as [OrderDtl2_PartNum],
[OrderDtl2].[CustNum] as [OrderDtl2_CustNum],
OrderHed1.OrderDate,OrderDtl2.DocUnitPrice,
LAG(OrderDtl2.DocUnitPrice,1) Over (Partition by OrderDtl2.PartNum, OrderDtl2.CustNum order by OrderHed1.SysRevID) as LastUnitPrice
from Erp.OrderDtl as OrderDtl2
inner join Erp.OrderHed as OrderHed1 on
OrderDtl2.Company = OrderHed1.Company
and OrderDtl2.OrderNum = OrderHed1.OrderNum
order by [OrderDtl2].[Company], [OrderDtl2].[PartNum], [OrderDtl2].[CustNum], OrderHed1.SysRevID
1 Like
You query does a lot of aggregations. Not sure if it will work the way you have the query written. As the final query doesn’t take into account the date of the order.
Looking at my picture what do you want part 00-184 to show as last unit price on the main query?
Do you have an example of the final output. It might be easier to see how this will come together.
Here is a picture:
The Doc unit cost is a max(orderdtl.docunitcost) not the last cost. I am trying to add the last unit cost to the query. So the top one would be LastUnitCost would be 53.25 when the new cost got reduced. (instead of 54.74)
ok back to my list then.
Would you want the DocUnitPrice on 3/5/2018 of $360 to show then for the line?
If there was a line in your picture of customer 31 and part 000-184
Yes
that would mean you want the last sales price for part customer. got it
There might be a better way, but I know this works.
Here is what you will need then as your subquery on the main.
select OrderDtl2_PartNum, OrderDtl2_CustNum, AVG(LastUnitPrice) as LastCostPartCust
from (select
[OrderDtl2].[Company] as [OrderDtl2_Company],
[OrderDtl2].[PartNum] as [OrderDtl2_PartNum],
[OrderDtl2].[CustNum] as [OrderDtl2_CustNum],
(first_value(OrderDtl2.DocUnitPrice) Over (Partition by OrderDtl2.Company, OrderDtl2.PartNum, OrderDtl2.CustNum order by OrderHed1.SysRevID desc)) as LastUnitPrice
from Erp.OrderDtl as OrderDtl2
inner join Erp.OrderHed as OrderHed1 on
OrderDtl2.Company = OrderHed1.Company
and OrderDtl2.OrderNum = OrderHed1.OrderNum
--where OrderDtl2.partnum = '000-184'
) as LastPriceCustPart
group by OrderDtl2_Company, OrderDtl2_PartNum, OrderDtl2_CustNum
1 Like
Thanks that worked like a charm… Thank you