SELECT P.ID AS ItemCode, P.KODIKOS AS PartNo, M.Manufacture AS BrandName, CASE N'en' WHEN 'el' THEN P.ONOMA COLLATE SQL_Latin1_General_CP1253_CI_AS ELSE P.ONOMAEN COLLATE SQL_Latin1_General_CP1253_CI_AS END AS Product, '' AS Description,P.Price AS UnitPrice, ISNULL(P.DISCOUNT, 0) AS Discount, (P.Price - (ISNULL(P.DISCOUNT, 0)/100.0)*P.Price) AS DiscountPrice, ISNULL((SELECT SUM(Quantity) FROM ORDERDETAILS OD WHERE OD.ProductID = P.ID), 0) AS TotalSales, dbo.ufn_JoinProductFilters(P.ID, 'en') AS filters, P.InStock AS AvailabilityFlag, CASE N'en' WHEN 'el' THEN C.CATEGORY COLLATE SQL_Latin1_General_CP1253_CI_AS ELSE C.CATEGORYEN COLLATE SQL_Latin1_General_CP1253_CI_AS END AS Category, P.KIT FROM Products P INNER JOIN Manufactures M ON P.Manufactures = M.MID INNER JOIN Categories C ON P.Categories = C.CID WHERE P.OnAir = 1 AND P.Price > 0 AND (P.ID IN (SELECT GAMEID FROM PRODUCTMODELS WHERE MOBILEID = 1137) OR 1137 = 0) AND P.Categories = 102 ORDER BY TotalSales DESC
