WITH cteSir(SaleOrder_number, TotalLeadDays, DueDate)
Select	SaleOrder.SaleOrder_number,
		IsNull(SUM(Delay.Days), 0) + Courier.LeadDays As TotalLeadDays,
		DATEADD(D, Courier.LeadDays, SaleOrderItem.GoodsReceived) DueDate
From	SaleOrder
Inner Join SaleOrderItem
	On SaleOrder.SaleOrder_ID = SaleOrderItem.SaleOrder_ID
	and SaleOrderItem.SaleOrder_ID not in (
								Select SaleOrder_ID
								From SaleOrderItem SOInner 
								Where SOInner.Courier_ID is null 
								Or SOInner.GoodsReceived is null
								)
/* Find any deliveries which are related to this order and courier. */
Left Outer Join	Delivery
	On SaleOrder.SaleOrder_ID = Delivery.SaleOrder_ID
	And	Delivery.Courier_ID = SaleOrderItem.Courier_ID	
/* Check which delivery delays have been enetered for this item. */ 
Left Outer Join	Delivery_Delay_Lnk_Item
		On SaleOrderItem.SaleOrderItem_ID = Delivery_Delay_Lnk_Item.Item_ID
		Left Join	Delay
			On Delivery_Delay_Lnk_Item.Delay_ID = Delay.Delay_ID
/* Determine the courier for the item. */
Inner Join Courier
	On	SaleOrderItem.Courier_ID = Courier.Courier_ID			
/* Check the order is alive and active. */						
Where	SaleOrder.Placed IS NOT NULL
And		SaleOrder.Active = 1
And		SaleOrder.Canceled IS NULL
/* Check that the order has not yet been delievred. */
And		Delivery.Delivered IS NULL
Group By	SaleOrder.SaleOrder_Number,
			Courier.LeadDays
)
SELECT SaleOrder_number, 
       TotalLeadDays, 
       MAX(DueDateOrder)
FROM cteSir
ORDER BY DueDate ASC