SELECT *,
SUM(BORÇ-ALACAK) OVER(PARTITION BY [CH KODU] ORDER BY TARIH ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) BAKIYE
FROM (
SELECT CLL.DATE_ AS [TARIH],
CLL.CANCELLED AS [IPTAL],
CASE
WHEN CLL.TRCODE = 31 THEN 'Mal Alım Fat.'
WHEN CLL.TRCODE = 32 THEN 'Perakende Satış İade Fat.'
WHEN CLL.TRCODE = 33 THEN 'Toptan Satış İade Fat.'
WHEN CLL.TRCODE = 34 THEN 'Alınan Hizmet Fat.'
WHEN CLL.TRCODE = 36 THEN 'Alım İade Fat.'
WHEN CLL.TRCODE = 37 THEN 'Perakende Satış Fat.'
WHEN CLL.TRCODE = 38 THEN 'Toptan Satış Fat.'
WHEN CLL.TRCODE = 39 THEN 'Verilen Hizmet Fat.'
WHEN CLL.TRCODE = 3 THEN 'Borç Dekontu'
WHEN CLL.TRCODE = 4 THEN 'Alacak Dekontu'
WHEN CLL.TRCODE = 5 THEN 'Virman İşlemi'
WHEN CLL.TRCODE = 14 THEN 'Açılış İşlemi'
WHEN CLL.TRCODE = 46 THEN 'Alınan Serbest Meslek Makbuzu'
WHEN CLL.TRCODE = 61 THEN 'Çek Girişi'
WHEN CLL.TRCODE = 62 THEN 'Senet Girişi'
WHEN CLL.TRCODE = 63 THEN 'Çek Çıkışı(Cari Hesaba)'
WHEN CLL.TRCODE = 64 THEN 'Senet Çıkış(Cari Hesaba)'
WHEN CLL.TRCODE = 20 THEN 'Gelen Havaleler'
WHEN CLL.TRCODE = 21 THEN 'Gönderilen Havaleler'
WHEN CLL.TRCODE = 16 THEN 'Banka Alınan Hizmet Fat.'
WHEN CLL.TRCODE = 1 THEN 'Nakit Tahsilat'
WHEN CLL.TRCODE = 2 THEN 'Nakit Ödeme'
WHEN CLL.TRCODE = 70 THEN 'Kredik Kartı Fişi'
WHEN CLL.TRCODE = 3 THEN 'Müşteriye İade Edilen Çekler'
WHEN CLL.TRCODE = 44 THEN 'Verilen Fiyat Farkı Faturası'
WHEN CLL.TRCODE = 4 THEN 'Müşteriden Portföye İade Çekler'
Else cast(CLL.trcode AS VARCHAR(3)) END AS [FIS TURU],
(CASE WHEN CLL.MODULENR = 10 THEN KSL.FICHENO ELSE CLL.TRANNO END) AS [FIS NO],
CLC.CODE AS [CH KODU],
CLC.DEFINITION_ AS [MUSTERI ADI],
CLL.LINEEXP AS [SATIR ACIKLAMASI],
(CASE WHEN CLL.SIGN=0 OR CLL.PAIDINCASH=1 THEN CLL.AMOUNT ELSE 0 END) AS [BORÇ],
(CASE WHEN CLL.SIGN=1 THEN CLL.AMOUNT ELSE 0 END ) AS [ALACAK]
FROM LG_006_CLCARD CLC
LEFT OUTER JOIN LG_006_01_CLFLINE CLL ON CLL.CLIENTREF = CLC.LOGICALREF
LEFT OUTER JOIN LG_006_01_CLFICHE CLF ON CLL.SOURCEFREF = CLF.LOGICALREF
LEFT OUTER JOIN LG_006_01_INVOICE INV ON CLL.SOURCEFREF = INV.LOGICALREF
LEFT OUTER JOIN LG_006_01_KSLINES KSL ON CLL.SOURCEFREF = KSL.LOGICALREF
LEFT OUTER JOIN LG_006_01_BNFLINE BNL ON CLL.SOURCEFREF = BNL.LOGICALREF
LEFT OUTER JOIN LG_006_01_CSTRANS CST ON CLL.SOURCEFREF = CST.LOGICALREF
LEFT OUTER JOIN LG_006_01_CSROLL CSR ON CLL.SOURCEFREF = CSR.LOGICALREF
LEFT OUTER JOIN LG_006_01_EMFICHE EMF ON EMF.LOGICALREF =
(CASE
WHEN CLL.MODULENR = 4 THEN INV.ACCFICHEREF
WHEN CLL.MODULENR = 5 THEN CLF.ACCFICHEREF
WHEN CLL.MODULENR = 6 THEN CSR.ACCFICHEREF
WHEN CLL.MODULENR = 7 THEN BNL.ACCFICHEREF
WHEN CLL.MODULENR = 10 THEN KSL.ACCFICHEREF
WHEN CLL.MODULENR = 61 THEN CSR.ACCFICHEREF
ELSE '' END)
LEFT OUTER JOIN L_CAPIUSER O ON O.NR = CLL.CAPIBLOCK_CREATEDBY
LEFT OUTER JOIN L_CAPIUSER D ON D.NR = CLL.CAPIBLOCK_MODIFIEDBY
WHERE CLL.PAIDINCASH=0 AND CLL.CANCELLED='0') AS TMP
