SQL Query – Orders Not Shipped With Next Expected Date

I am struggling with a query. I need to be able to show outstanding orders (not shipped).

An order item can have a date when the customer is expecting the item by. This can be NULL.

I have the following tables

SalesOrder

SalesOrderId (PK)

SalesOrderItem

SalesOrderItem (PK)  
SalesOrderId (FK)
Qty  
Description  
DateExpected (DateTime | Nullable)  

Shipment

ShipmentId (PK)  

ShipmentItem

ShipmentItemId (PK)  
ShipmentId (FK)  
SalesOrderItemId (FK)  
Qty

A sales order item can have many shipment items relating to it (for split shipping).

I need a list of outstanding orders with the next expected date for each. I am struggling because I am not sure of the most economic way of doing this.

I could select all of the orders that have calculated ShippedQty < calculated OrderedQty and then join that to the order items again to get MIN(DateExpected) but I think that would be quite cumbersome with a large dataset.

The columns I need from the query would be:

Query Result

SalesOrderId  
NextDateExpected (Date or NULL)

Any pointers appreciated.

Answer

A straightforward way with CTE and subselect could be

WITH cte as
(select SalesOrderID , Min(DateExpected) DateExpected 
    from SalesOrderItem o group by SalesOrderId, SalesOrderItem
    having sum(Qty) <> (select COALESCE(sum(qty),0) from ShipmentItem s where s.SalesOrderItem = o.SalesOrderItem) )

select SalesOrderId, Min(DateExpected) DateExpected from cte
group by SalesOrderId

db-fiddle

Feel free to use and amend the fiddle to improve your question.

But Mark B has a point int saying a boolen flag for finished SalesOrders would be good, but maybe such a query could help marking SalesOrders as finished periodicly…