DECLARE @SupplyOrderID AS BIGINT = 3323739
DECLARE @OrderCount AS INT = 10
DECLARE @Deviation AS float = 2

DECLARE @EntityID AS BIGINT
DECLARE @VendorID AS BIGINT
DECLARE @ItemIDs TABLE
(
	ItemID BIGINT
)


INSERT INTO @ItemIDs
SELECT ItemId FROM tbTRansactionSupplyOrderDetail tsod WITH (NOLOCK)
WHERE tsod.iTransaction_num = @SupplyOrderID
;


SELECT @EntityID=EntityID, @VendorID=sVendor_cd FROM tbTRansactionSupplyOrder
WHERE iTransaction_num = @SupplyOrderID
;

WITH TOPTEN AS (
SELECT tsod.ItemID,tsod.EntityID, tso.sVendor_cd, tsod.fOrdered_qty,  ROW_NUMBER() 
    over (
        PARTITION BY tsod.EntityID, tso.sVendor_cd, tsod.[ItemID] 
        order by tso.iTransaction_num DESC
    ) AS RowNo 
    FROM tbTRansactionSupplyOrder tso WITH (NOLOCK) 
		INNER JOIN tbTRansactionSupplyOrderDetail tsod WITH (NOLOCK) 
			ON tso.iTransaction_num = tsod.iTransaction_num AND tso.EntityID = tsod.EntityID AND tsod.ItemID = ANY (SELECT ItemID FROM @ItemIDs)  
				AND tsod.fOrdered_qty > 0 
				AND tso.EntityID=@EntityID 
				AND tso.sVendor_cd = @VendorID 
)






/*UPDATE vei
SET vei.MinOrderQty = CASE WHEN x.Average > @Deviation THEN x.Average - @Deviation ELSE 0 END,
	vei.MaxOrderQty = x.Average + @Deviation*/
	
SELECT  vei.VendorEntityItemID,
		vei.ItemID,
		vei.EntityID, 
		vei.VendorID,
		vei.VendorCode,
		vei.VendorDescription,
		vei.MinOrderQty AS [CurrentMinValue], 
		CASE WHEN x.Average > @Deviation THEN x.Average - @Deviation ELSE 0 END AS newMinValue,
		vei.MaxOrderQty AS CurrentMaxValue,
		x.Average + @Deviation AS newMaxValue
	
FROM
tbVendorEntityItem vei 
	INNER JOIN (
	
		SELECT ItemID, AVG(fOrdered_qty) AS Average, COUNT(ItemID) AS [Count]
		FROM TOPTEN 
		WHERE RowNo <= @OrderCount
		GROUP BY ItemID

	) AS x ON x.ItemID = vei.ItemId
WHERE vei.EntityID=@EntityID 
	AND vei.VendorID=@VendorID 
	AND x.[Count] = @OrderCount
GO