DECLARE @AvMode int
SELECT @AvMode = StockAvaliabityMode FROM SystemConfiguration;
select distinct TOP 8 count(*)as count1, pl.Title, x.SKUID from
(select
s.ID as SKUID, o.ID as orderID, oi.SKUID as y, p.ID as productID, oi.ReturnedQty, oi.Qty,
CASE WHEN s.DiscountID IS NOT NULL THEN 1 ELSE 0 END AS HasDiscount,
CASE WHEN s.QTY = 0 THEN 1 ELSE 0 END AS IsOutOfStock
from SKU s
join OrderItem oi on oi.SKUID = s.ID
join [ORDER] o on o.ID = oi.OrderID
join Product p on p.ID = s.ProductID
where s.ProductID <> 2) x
JOIN ProductLocalize pl ON pl.ProductID = x.productID AND LanguageID = 2
join StoreSKU ss on ss.SKUID = x.SKUID and ss.StoreID=2
join orderItem oi on oi.OrderID = x.orderID
join [ORDER] o on o.ID = x.OrderID AND o.Status <> 4 AND o.Status <> 6 AND ISNULL(oi.ReturnedQty, 0) < oi.Qty AND
(ss.QTY > 0 OR @AvMode = 2)
where oi.SKUID not in (x.y)
and o.ID in (select o.ID from SKU s
join OrderItem oi on oi.SKUID = s.ID
join [ORDER] o on o.ID = oi.OrderID
join Product p on p.ID = s.ProductID
where p.ID = 2)
group by pl.Title, x.SKUID, o.ID
order by count1