Here’s a query you may want to investigate and compare:
select
[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[OrderQty] as [OrderDtl_OrderQty],
[OrderDtl].[ExtPriceDtl] as [OrderDtl_ExtPriceDtl],
(OrderDtl.ExtPriceDtl / OrderDtl.OrderQty) as [Calculated_PricePer],
(sum(OrderDtl.OrderQty) over (partition by OrderNum order by OrderLine rows between unbounded preceding and current row)) as [Calculated_RunningQty],
(sum(OrderDtl.ExtPriceDtl) over (partition by OrderNum order by OrderLine rows between unbounded preceding and current row)) as [Calculated_RunningTotal],
(RunningTotal / RunningQty) as [Calculated_RunningPricePer]
from Erp.OrderDtl as OrderDtl
where (OrderDtl.OrderNum = @OrderNum)
Daryl
Thank you very much.
Got what I needed with your and everyone else’s help …
select
[OrderDtl].[Company] as [OrderDtl_Company],
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
[OrderDtl].[OrderLine] as [OrderDtl_OrderLine],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[OrderQty] as [OrderDtl_OrderQty],
[OrderDtl].[UnitPrice] as [OrderDtl_UnitPrice],
[OrderDtl].[ExtPriceDtl] as [OrderDtl_ExtPriceDtl],
(sum(OrderDtl.OrderQty) over (partition by OrderDtl.Company order by OrderDtl.Company rows between unbounded preceding and current row)) as [Calculated_TotalRunQty],
(sum(OrderDtl.ExtPriceDtl) over (partition by OrderDtl.Company order by OrderDtl.Company rows between unbounded preceding and current row)) as [Calculated_TotalExtPrice],
(sum(OrderDtl.ExtPriceDtl) over (partition by OrderDtl.Company order by OrderDtl.Company rows between unbounded preceding and current row) / sum(OrderDtl.OrderQty) over (partition by OrderDtl.Company order by OrderDtl.Company rows between unbounded preceding and current row)) as [Calculated_TotalAvgPrice]
from Erp.OrderDtl as OrderDtl
YES
I often forget how the calculated BAQ fields support an extensive amount of “SQL” coding
So it was nice to see the solution come out of the BAQ instead of a customized dashboard.
It’s a simpler solution
Thanks again to everyone for their kind assistance.
