WITH KALAN AS (

SELECT STOK_KODU,SUM(CASE WHEN STHAR_GCKOD='G' THEN STHAR_GCMIK ELSE -STHAR_GCMIK END) MIKTAR,

ROUND(SUM(CASE WHEN STHAR_GCKOD='G' THEN STHAR_GCMIK*(CASE WHEN STHAR_HTUR<>'L' THEN STHAR_NF ELSE STHAR_IAF END) 

ELSE -STHAR_GCMIK*(CASE WHEN STHAR_HTUR='L' THEN STHAR_NF ELSE STHAR_IAF END) END),2) TUTAR

FROM TBLSTHAR

WHERE STHAR_HTUR<>'B' AND STOK_KODU NOT LIKE 'HIZ%' AND STOK_KODU NOT IN ('FF' /*haric tutulacak stok kodlari */)

GROUP BY STOK_KODU

HAVING SUM(CASE WHEN STHAR_GCKOD='G' THEN STHAR_GCMIK ELSE -STHAR_GCMIK END) <> 0

)

, GIRISLER AS  (

SELECT TOP 100 PERCENT A.STOK_KODU,A.STHAR_TARIH,A.STHAR_HTUR,FISNO AS BELGENO,INCKEYNO,DEPO_KODU DEPO,STHAR_ACIKLAMA CARI_KOD,STHAR_FTIRSIP FTIR,ISNULL(A.VADE_TARIHI,A.STHAR_TARIH) VADE_TARIHI,

 STHAR_GCMIK HAR_MIK,B.MIKTAR KALAN,B.TUTAR KAL_TUT,

ROUND(CASE WHEN STHAR_HTUR <> 'L' THEN STHAR_GCMIK*STHAR_NF ELSE STHAR_GCMIK*STHAR_IAF END, 2) TUTAR,

DATEDIFF(d,STHAR_TARIH,ISNULL(A.VADE_TARIHI,A.STHAR_TARIH)) GUN,B.MIKTAR,

DATEDIFF(d,STHAR_TARIH,ISNULL(A.VADE_TARIHI,A.STHAR_TARIH)) * ROUND(CASE WHEN STHAR_HTUR <> 'L' THEN STHAR_GCMIK*STHAR_NF ELSE STHAR_GCMIK*STHAR_IAF END, 2) AS ADATB,

ROW_NUMBER() OVER (PARTITION BY A.STOK_KODU ORDER BY A.STOK_KODU,STHAR_TARIH DESC,INCKEYNO DESC) AS SIRA

FROM TBLSTHAR A (NOLOCK)

JOIN KALAN B ON A.STOK_KODU=B.STOK_KODU

WHERE STHAR_GCKOD='G' AND STHAR_HTUR NOT IN ('B','N') AND A.STOK_KODU NOT LIKE 'HIZ%'

ORDER BY A.STOK_KODU,A.STHAR_TARIH DESC,INCKEYNO DESC

),

GGIRIS AS (

SELECT G1.STOK_KODU,G1.STHAR_TARIH,G1.VADE_TARIHI,G1.HAR_MIK MIKTAR,G1.KALAN,G1.KAL_TUT,G1.ADATB,G1.TUTAR,G1.GUN,G1.SIRA,G1.INCKEYNO,G1.DEPO,G1.BELGENO,G1.FTIR,G1.CARI_KOD,

SUM(G2.HAR_MIK) MIKTART,SUM(G2.TUTAR) TTUTAR,SUM(G2.ADATB) ADAT, SUM(G2.GUN) TGUN

FROM GIRISLER G1

JOIN GIRISLER G2 ON G1.STOK_KODU=G2.STOK_KODU AND G2.SIRA <= G1.SIRA

GROUP BY G1.STOK_KODU,G1.STHAR_TARIH,G1.STHAR_TARIH,G1.BELGENO,G1.INCKEYNO,G1.FTIR,G1.CARI_KOD,G1.DEPO,G1.VADE_TARIHI,G1.HAR_MIK,G1.TUTAR,G1.GUN,G1.ADATB,G1.SIRA,G1.KALAN,G1.KAL_TUT

),

SONUCG AS (SELECT STOK_KODU,SIRA,STHAR_TARIH,BELGENO,CARI_KOD,DEPO,FTIR,VADE_TARIHI,TGUN,KALAN,KAL_TUT,MIKTAR,TUTAR,MIKTART TMIKTAR,

CASE WHEN KALAN BETWEEN MIKTART - MIKTAR AND MIKTART THEN KALAN ELSE MIKTART END MIKTART,

CASE WHEN KALAN BETWEEN MIKTART - MIKTAR AND MIKTART THEN KAL_TUT ELSE TTUTAR END TTUTAR,TTUTAR TUTAR_T,GUN,ADATB,ADAT,

CASE WHEN KALAN BETWEEN MIKTART - MIKTAR AND MIKTART THEN (ADAT - ADATB)+GUN*(TTUTAR - KAL_TUT) ELSE ADAT END ADAT2,INCKEYNO

FROM GGIRIS

WHERE (CASE WHEN (KALAN BETWEEN MIKTART - MIKTAR AND MIKTART) OR KALAN > MIKTART THEN 1 ELSE 0 END) = 1)

SELECT TOP 100 PERCENT A.STOK_KODU,KOD_5 RAPOR,STOK_ADI,

ROW_NUMBER() OVER (PARTITION BY A.STOK_KODU ORDER BY A.STOK_KODU,SIRA DESC) AS SIRA2,

STHAR_TARIH TARIH,BELGENO,CARI_KOD,FTIR,VADE_TARIHI,A.MIKTAR,TUTAR,GUN,MIKTART,TTUTAR,ADAT2,

CASE WHEN KAL_TUT<>0 THEN ADAT/TUTAR_T ELSE 0 END AS ORT_GUN ,KALAN,KAL_TUT,INCKEYNO,DEPO,DATEDIFF(d,VADE_TARIHI,GETDATE()) as DURG

FROM SONUCG A

JOIN TBLSTSABIT B ON A.STOK_KODU=B.STOK_KODU

ORDER BY STOK_KODU,SIRA 

GO