I'm trying to build a report that includes each stock item's next purchase order delivery requested date. So I'm trying to create a SQL view ideally returning
- ItemID
- NextRequestedDeliveryDate
- (Purchase Order) DocumentNo.
I'll then add the view to the data model so I can use it in reports.
So the relevant tables are:
- POPOnOrderBalance
- POPOnOrderLine
- POPStandardItemLink
- POPOrderReturnLine
- POPOrderReturn
- RequestedDeliveryDate
The warehouse is irrelevant for this particular report.
I'm finding it remarkably difficult!
First, POPOnOrderBalance and POPOnOrderLine seem unreliable - they don't always seem to agree with the POPOrderReturnLines.
Secondly, if there are multiple RequestedDeliveryDates for a line, how do I select the "next" one; ie. the date that takes into account the quantity already received? I've been playing around with "SUM(RequestedDeliveryDate.Quantity) OVER (PARTITION BY POPOrderReturnLineID ORDER BY DeliveryDate) AS CumulativeQty", to compare with POPOrderRerurnLine.ReceiptReturnQuantity, but haven't quite got it yet.
Thirdly, the quantities in RequestedDeliveryDate don't have to agree with POPOrderReturnLine.LineQuantity. So if there isn't a relevant RequestedDeliveryDate for the line, the view should return POPOrderReturn.RequestedDeliveryDate
Any help gratefully received!
Geoff
