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'