Untitled
SELECT DISTINCT Item.ItemCode AS [Ürün Kodu], Color.ColorDescription AS [Renk], Line.ItemDim1Code AS [Beden], bard.Barcode AS [Barkod], Att.ProductDescription AS [Ürün Adı], Att.ProductHierarchyLevel02 AS [Ürün Grubu], att.ProductAtt17Desc AS [Cinsiyet], Att.ProductAtt02Desc AS [Marka], CONVERT(NVARCHAR, Item.CreatedDate, 103) AS [Ürün Oluşturulma Tarihi], AlışFiyatı.[Alış Fiyat], SatışFiyatı.[Satış Fiyat], COALESCE(LastDaySiparis.LastDaySiparis, 0) AS [Dünki Satış], COALESCE(SevenDaysOrder.SevenDaysOrder, 0) AS [7 Günlük Satış], COALESCE(Seven2DaysOrder.Seven2DaysOrder, 0) AS [14 Günlük Satış], COALESCE(ThirtyDaysOrder.ThirtyDaysOrder, 0) AS [30 Günlük Satış], COALESCE(AllTimeOrder.AllTimeOrder, 0) AS [Tüm Zamanlar Satış], CASE WHEN (AvailableInventoryQty1.AvailableInventoryQty1 - RemainingOrderQty1.RemainingOrderQty1) IS NULL THEN AvailableInventoryQty1.AvailableInventoryQty1 ELSE (AvailableInventoryQty1.AvailableInventoryQty1 - RemainingOrderQty1.RemainingOrderQty1) END AS [Envanter], (AvailableInventoryQty1.AvailableInventoryQty1 - RemainingOrderQty1.RemainingOrderQty1) / (CASE WHEN SevenDaysOrder.SevenDaysOrder / 7 IS NULL THEN 1 ELSE SevenDaysOrder.SevenDaysOrder / 7 END ) AS [Bölüm sonuç], (CASE WHEN (AvailableInventoryQty1.AvailableInventoryQty1 - RemainingOrderQty1.RemainingOrderQty1) / (CASE WHEN SevenDaysOrder.SevenDaysOrder / 7 IS NULL THEN 1 ELSE SevenDaysOrder.SevenDaysOrder / 7 END ) BETWEEN 0 AND 10 AND SevenDaysOrder.SevenDaysOrder >0 THEN 'Sipariş Geç' ELSE 'Sipariş Geçme' END ) AS [RPT BİLGİSİ] FROM dbo.trStock Line INNER JOIN ProductFilterWithDescription('TR') Att ON Line.ItemCode = Att.ProductCode INNER JOIN dbo.cdColorDesc Color ON Line.ColorCode = Color.ColorCode INNER JOIN dbo.cdItem Item ON Item.ItemCode = Line.ItemCode INNER JOIN ( SELECT ItemCode, MAX(Price) AS [Alış Fiyat] FROM dbo.prItemBasePrice WHERE BasePriceCode = 2 GROUP BY ItemCode ) AS AlışFiyatı ON AlışFiyatı.ItemCode = Line.ItemCode INNER JOIN ( SELECT ItemCode, MAX(Price) AS [Satış Fiyat] FROM dbo.prItemBasePrice WHERE BasePriceCode = 7 GROUP BY ItemCode ) AS SatışFiyatı ON SatışFiyatı.ItemCode = Line.ItemCode LEFT JOIN ( SELECT ItemCode, ColorCode, ItemDim1Code, SUM(Qty1) AS [LastDaySiparis] FROM dbo.AllOrderLines WHERE CreatedDate BETWEEN DATEADD(DAY, -1, CONVERT(DATE, GETDATE())) AND CONVERT(DATE, GETDATE()) GROUP BY ItemCode, ColorCode, ItemDim1Code ) AS LastDaySiparis ON LastDaySiparis.ItemCode = Line.ItemCode AND LastDaySiparis.ColorCode = Line.ColorCode AND LastDaySiparis.ItemDim1Code = Line.ItemDim1Code LEFT JOIN ( SELECT ItemCode, ColorCode, ItemDim1Code, SUM(Qty1) AS [SevenDaysOrder] FROM dbo.AllOrderLines WHERE CreatedDate BETWEEN DATEADD(DAY, -7, CONVERT(DATE, GETDATE())) AND CONVERT(DATE, GETDATE()) GROUP BY ItemCode, ColorCode, ItemDim1Code ) AS SevenDaysOrder ON SevenDaysOrder.ItemCode = Line.ItemCode AND SevenDaysOrder.ColorCode = Line.ColorCode AND SevenDaysOrder.ItemDim1Code = Line.ItemDim1Code LEFT JOIN ( SELECT ItemCode, ColorCode, ItemDim1Code, SUM(Qty1) AS [Seven2DaysOrder] FROM dbo.AllOrderLines WHERE CreatedDate BETWEEN DATEADD(DAY, -14, CONVERT(DATE, GETDATE())) AND CONVERT(DATE, GETDATE()) GROUP BY ItemCode, ColorCode, ItemDim1Code ) AS Seven2DaysOrder ON Seven2DaysOrder.ItemCode = Line.ItemCode AND Seven2DaysOrder.ColorCode = Line.ColorCode AND Seven2DaysOrder.ItemDim1Code = Line.ItemDim1Code LEFT JOIN ( SELECT ItemCode, ColorCode, ItemDim1Code, SUM(Qty1) AS [ThirtyDaysOrder] FROM dbo.AllOrderLines WHERE CreatedDate BETWEEN DATEADD(DAY, -30, CONVERT(DATE, GETDATE())) AND CONVERT(DATE, GETDATE()) GROUP BY ItemCode, ColorCode, ItemDim1Code ) AS ThirtyDaysOrder ON ThirtyDaysOrder.ItemCode = Line.ItemCode AND ThirtyDaysOrder.ColorCode = Line.ColorCode AND ThirtyDaysOrder.ItemDim1Code = Line.ItemDim1Code LEFT JOIN ( SELECT ItemCode, ColorCode, ItemDim1Code, SUM(Qty1) AS [AllTimeOrder] FROM dbo.AllOrderLines WHERE CreatedDate BETWEEN DATEADD(DAY, -9999, CONVERT(DATE, GETDATE())) AND CONVERT(DATE, GETDATE()) GROUP BY ItemCode, ColorCode, ItemDim1Code ) AS AllTimeOrder ON AllTimeOrder.ItemCode = Line.ItemCode AND AllTimeOrder.ColorCode = Line.ColorCode AND AllTimeOrder.ItemDim1Code = Line.ItemDim1Code LEFT JOIN ( SELECT ItemCode, ColorCode, ItemDim1Code, SUM(In_Qty1) - SUM(Out_Qty1) AS [AvailableInventoryQty1] FROM trStock WHERE WarehouseCode = 'INT' GROUP BY ItemCode, ColorCode, ItemDim1Code ) AS AvailableInventoryQty1 ON AvailableInventoryQty1.ItemCode = Line.ItemCode AND AvailableInventoryQty1.ColorCode = Line.ColorCode AND AvailableInventoryQty1.ItemDim1Code = Line.ItemDim1Code LEFT JOIN ( SELECT ItemCode, ColorCode, ItemDim1Code, SUM(RemainingOrderQty1) AS [RemainingOrderQty1] FROM dbo.OrderStatus() WHERE WarehouseCode = 'INT' GROUP BY ItemCode, ColorCode, ItemDim1Code ) AS RemainingOrderQty1 ON RemainingOrderQty1.ItemCode = Line.ItemCode AND RemainingOrderQty1.ColorCode = Line.ColorCode AND RemainingOrderQty1.ItemDim1Code = Line.ItemDim1Code LEFT JOIN dbo.prItemBarcode bard ON bard.ItemCode=line.ItemCode AND bard.ColorCode=line.ColorCode AND bard.ItemDim1Code=line.ItemDim1Code WHERE Color.LangCode = 'TR'
Leave a Comment