I need a BAQ that returns the unit price of any partnum from the OrderDtl table where if there are any associated releases in the OrderRel table that are >= the current date I get the reqdate of the release closest to the current date in the future, and if only releases < the current date exist for a part number I get the latest reqdate in the past.
@matthew.heck Off the top of my head I think this will work. Two subqueries with a top query grouped by OrderDtl PartNum
Start with just a query on OrderRel open releases with a reqdate > today group by part with a calculated field MIN(OrderRel.ReqDate).
Once that is working make it a sub query and make another top query. This one is OrderRel ReqDate < Today group by part with a calculated field MAX(OrderRel.ReqDate) Once it is working make it a sub query.
Now for the top query. OrderDtl group by PartNum, join the two sub queries as all rows from OrderDtl.
The price field will be a calculated with a case statement that checks for a ReqDate from the future sub query first and then from the past with an Else of 0
CASE
When FutureRel.ReqDate is not null Then OrderDtl.DocUnitPrice
When PastRel.ReqDate is not null Then OrderDtl.DocUnitPrice
Else 0
End
1 Like
Double check that the orderrel.reqdate is actually populated. I seem to recall that dates on lines and releases could be null, which means that the date is on the parent. And that the date field is only set if it differs from the parent.
And what I mean by ‘parent’ is the next higher object. OrderHed is the parent of OrderDtl. Just like OrderDtl is to OrderRel.
Here is what I ended up doing:
select
[RetrieveOrderRel_AddRowNum].[OrderRel_Company] as [OrderRel_Company],
[RetrieveOrderRel_AddRowNum].[Customer_Name] as [Customer_Name],
[RetrieveOrderRel_AddRowNum].[OrderRel_PartNum] as [OrderRel_PartNum],
[RetrieveOrderRel_AddRowNum].[OrderRel_OrderNum] as [OrderRel_OrderNum],
[RetrieveOrderRel_AddRowNum].[OrderRel_OrderLine] as [OrderRel_OrderLine],
[RetrieveOrderRel_AddRowNum].[OrderRel_OrderRelNum] as [OrderRel_OrderRelNum],
[RetrieveOrderRel_AddRowNum].[OrderRel_ReqDate] as [OrderRel_ReqDate],
[RetrieveOrderRel_AddRowNum].[OrderDtl_DocUnitPrice] as [OrderDtl_DocUnitPrice]
from (select
[OrderRel].[Company] as [OrderRel_Company],
[Customer].[Name] as [Customer_Name],
[OrderRel].[PartNum] as [OrderRel_PartNum],
[OrderRel].[OrderNum] as [OrderRel_OrderNum],
[OrderRel].[OrderLine] as [OrderRel_OrderLine],
[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
[OrderRel].[ReqDate] as [OrderRel_ReqDate],
[OrderDtl].[DocUnitPrice] as [OrderDtl_DocUnitPrice],
[OrderRel].[SellingReqQty] as [OrderRel_SellingReqQty],
(ROW_NUMBER() OVER (PARTITION BY OrderRel.PartNum ORDER BY CASE WHEN OrderRel.ReqDate >= GETDATE() THEN DATEDIFF(day,GETDATE(),OrderRel.ReqDate) ELSE DATEDIFF(day,OrderRel.ReqDate,GETDATE()) END)) as [Calculated_RowNum]
from Erp.OrderRel as OrderRel
inner join Erp.OrderDtl as OrderDtl on
OrderRel.Company = OrderDtl.Company
and OrderRel.OrderNum = OrderDtl.OrderNum
and OrderRel.OrderLine = OrderDtl.OrderLine
and ( OrderDtl.DocUnitPrice <> 0 )
inner join Erp.Customer as Customer on
OrderDtl.Company = Customer.Company
and OrderDtl.CustNum = Customer.CustNum
where (OrderRel.ReqDate is not null)) as RetrieveOrderRel_AddRowNum
where (RetrieveOrderRel_AddRowNum.Calculated_RowNum = 1)
The inner select statement gets all the order releases and applies a row number using a window function with some variable order by clauses to take care of the current/future orders versus past orders. In use is also the partition by clause to partition the row numbering sequence by part number.
Then the outer select statement returns all only the order releases with a row number of 1 which gives me my listing of only the order release “closest” to today. “Closest” in this context means that if a part number has orders with a reqdate of equal to or greater than today then the closest date equal to or greater than today is returned. If a part number only has orders with a reqdate less than today then most recent reqdate in the past is returned.
1 Like