SELECT TOP 100 PERCENT
  X.Ay,
  X.Gun,
  X.Ambar,
  x.Stok_AktifPasif,
  x.Stok_Kodu,
  x.Stok_Adı,
  x.MalzemeAciklamasi2,
  x.MalzemeAciklamasi3,
  x.MalzemeAciklamasi4,
  x.[Bırım],
  x.Malzeme_Turu,
  x.GrupKodu,
  x.OzelKod,
  x.OzelKod2,
  x.OzelKod3,
  x.OzelKod4,
  x.OzelKod5, 
  GunHareketMiktar =SUM([Miktar]),
  Stok = round((select sum(CAST((CASE WHEN S.IOCODE IN (1, 2) THEN 1 ELSE -1  END) * S.AMOUNT * (CASE WHEN ISNULL(UINFO2, 0) = 0 THEN 1 ELSE UINFO2 END) / (CASE WHEN ISNULL(UINFO1, 0) = 0 THEN 1 ELSE UINFO1 END) AS float)) 
			from LG_211_01_STLINE S where S.STFICHEREF IN (SELECT STF.LOGICALREF FROM LG_211_01_STFICHE STF 
			WHERE 
				STF.LOGICALREF = S.STFICHEREF AND STF.CANCELLED=0) AND S.STOCKREF=X.ITEMLOGICALREF AND S.DATE_<=X.Gun AND S.SOURCEINDEX=X.SOURCEINDEX AND S.CANCELLED = 0 AND S.LINETYPE = 0 AND S.IOCODE IN (1, 2, 3, 4) AND S.LPRODSTAT = 0 ),4)
FROM (SELECT
STLINE.SOURCEINDEX,
ITEMLOGICALREF=STLINE.STOCKREF,
Ay=CAST(YEAR(STFICHE.DATE_) AS VARCHAR)+(CASE WHEN LEN(MONTH(STFICHE.DATE_))=1 THEN '0'+CAST(MONTH(STFICHE.DATE_) AS VARCHAR) ELSE CAST(MONTH(STFICHE.DATE_) AS VARCHAR) END),
Gun=STLINE.DATE_,
STFICHE.FICHENO,
  Ambar = (SELECT
    CAST(NR AS varchar) + '-' + NAME
  FROM L_CAPIWHOUSE CAPIWHOUSE
  WHERE CAPIWHOUSE.NR = STLINE.SOURCEINDEX
  AND CAPIWHOUSE.FIRMNR = 211),
  Isyeri = (SELECT
    CAST(NR AS varchar) + '-' + NAME
  FROM L_CAPIDIV CAPIDIV
  WHERE CAPIDIV.NR = STFICHE.BRANCH
  AND CAPIDIV.FIRMNR = 211),
  STFICHE.LOGICALREF [STFICHE_REFERANS],
  Stok_AktifPasif =case when ITEMS.ACTIVE=0 then 'Aktif' else 'Pasif' end,
  Stok_Kodu = ITEMS.CODE,
  Stok_Adı = ITEMS.NAME,
  MalzemeAciklamasi2 = ITEMS.NAME2,
  MalzemeAciklamasi3 = ITEMS.NAME3,
  MalzemeAciklamasi4 = ITEMS.NAME4,
  [Bırım] = BIRIM.CODE,
  Malzeme_Turu =
                CASE ITEMS.CARDTYPE
                  WHEN 1 THEN 'TICARI MAL'
                  WHEN 2 THEN 'KARMA KOLI'
                  WHEN 3 THEN 'DEPOZITOLU MAL'
                  WHEN 4 THEN 'SABIT KIYMET'
                  WHEN 10 THEN 'HAMMADDE'
                  WHEN 11 THEN 'YARI MAMUL'
                  WHEN 12 THEN 'MAMUL'
                  WHEN 13 THEN 'TUKETIM MALI'
                  WHEN 20 THEN 'GENEL MALZEME SINIFI'
                  WHEN 21 THEN 'TABLOLU MALZEME SINIFI'
                  ELSE CONVERT(varchar(20), CARDTYPE)
                END,
  GrupKodu = ITEMS.STGRPCODE,
  OzelKod = ITEMS.SPECODE,
  OzelKod2 = ITEMS.SPECODE2,
  OzelKod3 = ITEMS.SPECODE3,
  OzelKod4 = ITEMS.SPECODE4,
  OzelKod5 = ITEMS.SPECODE5,
  [Miktar] = CAST((CASE
    WHEN STLINE.IOCODE IN (1, 2) THEN 1
    ELSE -1
  END) * STLINE.AMOUNT * (CASE
    WHEN ISNULL(UINFO2, 0) = 0 THEN 1
    ELSE UINFO2
  END) / (CASE
    WHEN ISNULL(UINFO1, 0) = 0 THEN 1
    ELSE UINFO1
  END) AS float)
FROM LG_211_01_STFICHE STFICHE WITH (NOLOCK)
LEFT OUTER JOIN LG_211_01_STLINE STLINE WITH (NOLOCK)
  ON STFICHE.LOGICALREF = STLINE.STFICHEREF
LEFT OUTER JOIN LG_211_ITEMS ITEMS WITH (NOLOCK)
  ON STLINE.STOCKREF = ITEMS.LOGICALREF
LEFT OUTER JOIN LG_211_UNITSETL BIRIM WITH (NOLOCK)
  ON BIRIM.UNITSETREF = ITEMS.UNITSETREF
  AND BIRIM.MAINUNIT = 1
WHERE STFICHE.CANCELLED = 0
AND STLINE.CANCELLED = 0
AND STLINE.LINETYPE = 0
AND STLINE.IOCODE IN (1, 2, 3, 4) 
AND STLINE.LPRODSTAT = 0
 
AND YEAR(STLINE.DATE_)>=2021 AND MONTH(STLINE.DATE_)=10  ) AS X
GROUP BY  
		 X.Ay,
		 X.Gun,
         X.Ambar,
		 x.Stok_AktifPasif,
		 x.SOURCEINDEX,
		 x.ITEMLOGICALREF,
         x.Stok_Kodu,
         x.Stok_Adı,
         x.MalzemeAciklamasi2,
         x.MalzemeAciklamasi3,
         x.MalzemeAciklamasi4,
         x.Bırım,
         x.Malzeme_Turu,
         x.GrupKodu,
         x.OzelKod,
         x.OzelKod2,
         x.OzelKod3,
         x.OzelKod4,
         x.OzelKod5 
		 ORDER BY  X.Stok_Kodu ,  X.Ambar, X.Gun