SELECT
LG_017_SPECODES.DEFINITION_ as [İlgili Marka],
LG_017_ITEMS.STGRPCODE as [Grup Kodu],
LG_017_MARKET.REYONCODE as [Reyon Kodu],
LG_017_ITEMS.CODE AS [Malzeme (Sınıfı) Kodu],
LG_017_ITEMS.NAME AS [Malzeme (Sınıfı) Açıkl.],
LG_017_UNITSETF.NAME AS [Ana Birim],
ISNULL(SUM(SONSTOK.[Eldeki Miktar]),0) AS [Stok Miktarı],
case when SUM(FATURA_BILGISI_AY2.TOPLAM)=1 then SUM(FATURA_BILGISI_AY.SATISTOPLAM) else 0 end +
CASE WHEN SUM(FATURA_BILGISI_AY2.TOPLAM)>1 THEN CASE WHEN FATURA_BILGISI_AY2.gun BETWEEN 32 AND 59 THEN 1.0 ELSE 0.0 END * SUM(FATURA_BILGISI_AY.SATISTOPLAM)/2 else 0 end-
CASE WHEN SUM(FATURA_BILGISI_AY2.TOPLAM)>1 THEN CASE WHEN FATURA_BILGISI_AY2.gun BETWEEN 60 AND 89 THEN 1.0 ELSE 0.0 END * SUM(FATURA_BILGISI_AY.SATISTOPLAM)/3 else 0 end +
CASE WHEN SUM(FATURA_BILGISI_AY2.TOPLAM)>1 THEN CASE WHEN FATURA_BILGISI_AY2.gun BETWEEN 90 AND 119 THEN 1.0 ELSE 0.0 END * SUM(FATURA_BILGISI_AY.SATISTOPLAM)/4 else 0 end +
CASE WHEN SUM(FATURA_BILGISI_AY2.TOPLAM)>1 THEN CASE WHEN FATURA_BILGISI_AY2.gun BETWEEN 120 AND 190 THEN 1.0 ELSE 0.0 END * SUM(FATURA_BILGISI_AY.SATISTOPLAM)/6 else 0 end
as [Aylık Ortalama Çıkış Miktarı],
case when SUM(FATURA_BILGISI_AY2.TOPLAM)=1 then SUM(FATURA_BILGISI_AY.SATISTOPLAM) else 0 end *4+
CASE WHEN SUM(FATURA_BILGISI_AY2.TOPLAM)>1 THEN CASE WHEN FATURA_BILGISI_AY2.gun BETWEEN 32 AND 59 THEN 1.0 ELSE 0.0 END * SUM(FATURA_BILGISI_AY.SATISTOPLAM)/2 else 0 end*4-
CASE WHEN SUM(FATURA_BILGISI_AY2.TOPLAM)>1 THEN CASE WHEN FATURA_BILGISI_AY2.gun BETWEEN 60 AND 89 THEN 1.0 ELSE 0.0 END * SUM(FATURA_BILGISI_AY.SATISTOPLAM)/3 else 0 end *4 -
CASE WHEN SUM(FATURA_BILGISI_AY2.TOPLAM)>1 THEN CASE WHEN FATURA_BILGISI_AY2.gun BETWEEN 90 AND 119 THEN 1.0 ELSE 0.0 END * SUM(FATURA_BILGISI_AY.SATISTOPLAM)/4 else 0 end *4 +
CASE WHEN SUM(FATURA_BILGISI_AY2.TOPLAM)>1 THEN CASE WHEN FATURA_BILGISI_AY2.gun BETWEEN 120 AND 190 THEN 1.0 ELSE 0.0 END * SUM(FATURA_BILGISI_AY.SATISTOPLAM)/6 else 0 end *4
AS [Güvenli Stok Miktarı] ,
ISNULL(SUM(SONSTOK.[Eldeki Miktar])-
case when SUM(FATURA_BILGISI_AY2.TOPLAM)=1 then SUM(FATURA_BILGISI_AY.SATISTOPLAM) else 0 end *4 -
CASE WHEN SUM(FATURA_BILGISI_AY2.TOPLAM)>1 THEN CASE WHEN FATURA_BILGISI_AY2.gun BETWEEN 32 AND 59 THEN 1.0 ELSE 0.0 END * SUM(FATURA_BILGISI_AY.SATISTOPLAM)/2 else 0 end*4-
CASE WHEN SUM(FATURA_BILGISI_AY2.TOPLAM)>1 THEN CASE WHEN FATURA_BILGISI_AY2.gun BETWEEN 60 AND 89 THEN 1.0 ELSE 0.0 END * SUM(FATURA_BILGISI_AY.SATISTOPLAM)/3 else 0 end *4 -
CASE WHEN SUM(FATURA_BILGISI_AY2.TOPLAM)>1 THEN CASE WHEN FATURA_BILGISI_AY2.gun BETWEEN 90 AND 119 THEN 1.0 ELSE 0.0 END * SUM(FATURA_BILGISI_AY.SATISTOPLAM)/4 else 0 end *4 -
CASE WHEN SUM(FATURA_BILGISI_AY2.TOPLAM)>1 THEN CASE WHEN FATURA_BILGISI_AY2.gun BETWEEN 120 AND 190 THEN 1.0 ELSE 0.0 END * SUM(FATURA_BILGISI_AY.SATISTOPLAM)/6 else 0 end *4 ,0 )
AS [Gereken Stok Miktarı]
FROM dbo.LG_017_ITEMS AS LG_017_ITEMS
FULL JOIN dbo.LG_017_SPECODES AS LG_017_SPECODES ON LG_017_ITEMS.SPECODE5= LG_017_SPECODES.SPECODE
INNER JOIN dbo.LG_017_UNITSETF AS LG_017_UNITSETF ON LG_017_ITEMS.UNITSETREF = LG_017_UNITSETF.LOGICALREF
FULL JOIN dbo.LG_017_MARKET AS LG_017_MARKET ON LG_017_ITEMS.LOGICALREF= LG_017_MARKET.ITEMREF
LEFT OUTER JOIN
(
SELECT TOP (100) PERCENT TOT.ONHAND AS [Eldeki Miktar],
(SELECT TOP (1) LINENET / AMOUNT * 10000 / 10000 AS Expr1
FROM dbo.LG_017_01_STLINE
WHERE (TRCODE IN (1)) AND (LPRODSTAT = 0) AND (LINETYPE = 0) OR
(TRCODE IN (1, 14)) AND (LPRODSTAT = 0) AND (LINETYPE = 0) AND (BILLED = 0)
ORDER BY DATE_ DESC) AS [Son ALIŞ], TOT.STOCKREF
FROM dbo.LV_017_01_GNTOTST AS TOT
WHERE (TOT.INVENNO = 0) ) AS SONSTOK ON SONSTOK.STOCKREF = LG_017_ITEMS.LOGICALREF
LEFT OUTER JOIN(SELECT STLINE.STOCKREF,
SUM(CASE WHEN MONTH(STLINE.DATE_) = 1 THEN CASE WHEN STLINE.IOCODE IN (4) THEN 1.0 ELSE - 1.0 END * STLINE.AMOUNT END)AS ADET01,
SUM(CASE WHEN MONTH(STLINE.DATE_) = 2 THEN CASE WHEN STLINE.IOCODE IN (4) THEN 1.0 ELSE - 1.0 END * STLINE.AMOUNT END)AS ADET02,
SUM(CASE WHEN MONTH(STLINE.DATE_) = 3 THEN CASE WHEN STLINE.IOCODE IN (4) THEN 1.0 ELSE - 1.0 END * STLINE.AMOUNT END)AS ADET03,
SUM(CASE WHEN MONTH(STLINE.DATE_) = 4 THEN CASE WHEN STLINE.IOCODE IN (4) THEN 1.0 ELSE - 1.0 END * STLINE.AMOUNT END)AS ADET04,
SUM(CASE WHEN MONTH(STLINE.DATE_) = 5 THEN CASE WHEN STLINE.IOCODE IN (4) THEN 1.0 ELSE - 1.0 END * STLINE.AMOUNT END)AS ADET05,
SUM(CASE WHEN MONTH(STLINE.DATE_) = 6 THEN CASE WHEN STLINE.IOCODE IN (4) THEN 1.0 ELSE - 1.0 END * STLINE.AMOUNT END)AS ADET06,
SUM(CASE WHEN MONTH(STLINE.DATE_) = 7 THEN CASE WHEN STLINE.IOCODE IN (4) THEN 1.0 ELSE - 1.0 END * STLINE.AMOUNT END)AS ADET07,
SUM(CASE WHEN MONTH(STLINE.DATE_) = 8 THEN CASE WHEN STLINE.IOCODE IN (4) THEN 1.0 ELSE - 1.0 END * STLINE.AMOUNT END)AS ADET08,
SUM(CASE WHEN MONTH(STLINE.DATE_) = 9 THEN CASE WHEN STLINE.IOCODE IN (4) THEN 1.0 ELSE - 1.0 END * STLINE.AMOUNT END)AS ADET09,
SUM(CASE WHEN MONTH(STLINE.DATE_) = 10 THEN CASE WHEN STLINE.IOCODE IN (4) THEN 1.0 ELSE - 1.0 END * STLINE.AMOUNT END)AS ADET10,
SUM(CASE WHEN MONTH(STLINE.DATE_) =11 THEN CASE WHEN STLINE.IOCODE IN (4) THEN 1.0 ELSE - 1.0 END * STLINE.AMOUNT END)AS ADET11,
SUM(CASE WHEN MONTH(STLINE.DATE_) = 12 THEN CASE WHEN STLINE.IOCODE IN (4) THEN 1.0 ELSE - 1.0 END * STLINE.AMOUNT END)AS ADET12,
SUM(ISNULL((CASE WHEN STLINE.IOCODE IN (4) THEN 1.0 ELSE 1.0 END) * STLINE.AMOUNT, 0)) AS [SATISTOPLAM]
FROM dbo.LG_017_01_STLINE AS STLINE WITH (NOLOCK)
WHERE (STLINE.CANCELLED = 0) and STLINE.IOCODE IN (4)
and STLINE.DATE_ BETWEEN (DATEADD(day,-180,GETDATE())) AND (GETDATE())
GROUP BY STLINE.STOCKREF
) AS FATURA_BILGISI_AY ON FATURA_BILGISI_AY.STOCKREF = LG_017_ITEMS.LOGICALREF
LEFT OUTER JOIN(SELECT STLINE.STOCKREF,
MAX(CASE WHEN MONTH(STLINE.DATE_) = 1 THEN 1 ELSE 0 END) +
MAX(CASE WHEN MONTH(STLINE.DATE_) = 2 THEN 1 ELSE 0 END) +
MAX(CASE WHEN MONTH(STLINE.DATE_) = 3 THEN 1 ELSE 0 END) +
MAX(CASE WHEN MONTH(STLINE.DATE_) = 4 THEN 1 ELSE 0 END) +
MAX(CASE WHEN MONTH(STLINE.DATE_) = 5 THEN 1 ELSE 0 END) +
MAX(CASE WHEN MONTH(STLINE.DATE_) = 6 THEN 1 ELSE 0 END) +
MAX(CASE WHEN MONTH(STLINE.DATE_) = 7 THEN 1 ELSE 0 END) +
MAX(CASE WHEN MONTH(STLINE.DATE_) = 8 THEN 1 ELSE 0 END) +
MAX(CASE WHEN MONTH(STLINE.DATE_) = 9 THEN 1 ELSE 0 END) +
MAX(CASE WHEN MONTH(STLINE.DATE_) = 10 THEN 1 ELSE 0 END) +
MAX(CASE WHEN MONTH(STLINE.DATE_) = 11 THEN 1 ELSE 0 END) +
MAX(CASE WHEN MONTH(STLINE.DATE_) = 12 THEN 1 ELSE 0 END) AS [TOPLAM],
MAX(DATEDIFF(day,STLINE.DATE_,GETDATE())) as [gun]
FROM dbo.LG_017_01_STLINE AS STLINE WITH (NOLOCK)
WHERE (STLINE.CANCELLED = 0) and STLINE.IOCODE IN (4)
and STLINE.DATE_ BETWEEN (DATEADD(day,-180,GETDATE())) AND (GETDATE()) AND STLINE.IOCODE IN (4)
GROUP BY STLINE.STOCKREF
) AS FATURA_BILGISI_AY2 ON FATURA_BILGISI_AY2.STOCKREF = LG_017_ITEMS.LOGICALREF
/*son*/
WHERE (LG_017_ITEMS.ACTIVE = 0) and ( LG_017_ITEMS.SPECODE4='Hammadde')
GROUP BY LG_017_ITEMS.STGRPCODE,
LG_017_ITEMS.CODE,
LG_017_ITEMS.NAME,
LG_017_SPECODES.DEFINITION_,
LG_017_UNITSETF.NAME,
LG_017_MARKET.REYONCODE,
FATURA_BILGISI_AY2.gun