SQL Sorgu Cari Ekstre Raporu

SELECT CLL.DATE_ AS [TARİH],  CLF.SPECCODE AS [BELGE NO],
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=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 [FİŞTÜRÜ],
(CASE WHEN CLL.MODULENR=10 THEN KSL.FICHENO ELSE CLL.TRANNO END) AS [FİŞ NO],
(CASE
WHEN CLL.MODULENR=4  AND INV.ACCFICHEREF >0  THEN EMF.FICHENO
WHEN CLL.MODULENR=5  AND CLF.ACCFICHEREF >0  THEN EMF.FICHENO
WHEN CLL.MODULENR=6  AND CSR.ACCFICHEREF >0  THEN EMF.FICHENO
WHEN CLL.MODULENR=7  AND BNL.ACCFICHEREF >0  THEN EMF.FICHENO
WHEN CLL.MODULENR=10 AND KSL.ACCFICHEREF >0  THEN EMF.FICHENO
WHEN CLL.MODULENR=61 AND CSR.ACCFICHEREF >0  THEN EMF.FICHENO Else '' END) AS [MUH.FİŞNO] ,
CLC.CODE AS [CH KODU],
CLC.DEFINITION_ AS [MÜŞTERİ ADI],
CLL.LINEEXP AS [SATIR AÇIKLAMASI],
CLF.GENEXP1 AS [İŞLEM AÇIKLAMASI],
(CASE WHEN INV.FROMKASA=1 THEN (CLL.SIGN)*CLL.AMOUNT  ELSE (1-CLL.SIGN)*CLL.AMOUNT END ) AS [BORÇ],
(CLL.SIGN*CLL.AMOUNT)  AS [ALACAK]
FROM lg_223_CLCARD CLC
LEFT OUTER JOIN lg_223_01_CLFLINE CLL  ON CLL.CLIENTREF  =CLC.LOGICALREF
LEFT OUTER JOIN lg_223_01_CLFICHE CLF  ON CLL.SOURCEFREF = CLF.LOGICALREF
LEFT OUTER JOIN lg_223_01_INVOICE INV  ON CLL.SOURCEFREF =INV.LOGICALREF
LEFT OUTER JOIN lg_223_01_KSLINES KSL  ON CLL.SOURCEFREF =KSL.LOGICALREF
LEFT OUTER JOIN lg_223_01_BNFLINE BNL  ON CLL.SOURCEFREF =BNL.LOGICALREF
LEFT OUTER JOIN lg_223_01_CSTRANS  CST ON CLL.SOURCEFREF =CST.LOGICALREF
LEFT OUTER JOIN lg_223_01_CSROLL CSR   ON CLL.SOURCEFREF =CSR.LOGICALREF
LEFT OUTER JOIN lg_223_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.DATE_  BETWEEN '01-01-2018' AND '31-12-2018')
ORDER BY CLL.DATE_, CLL.MODULENR, CLL.TRANNO

SELECT CLL.DATE_ AS [TARİH],  CLF.SPECCODE AS [BELGE NO],
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=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 [FİŞTÜRÜ],
(CASE WHEN CLL.MODULENR=10 THEN KSL.FICHENO ELSE CLL.TRANNO END) AS [FİŞ NO],
(CASE
WHEN CLL.MODULENR=4  AND INV.ACCFICHEREF >0  THEN EMF.FICHENO
WHEN CLL.MODULENR=5  AND CLF.ACCFICHEREF >0  THEN EMF.FICHENO
WHEN CLL.MODULENR=6  AND CSR.ACCFICHEREF >0  THEN EMF.FICHENO
WHEN CLL.MODULENR=7  AND BNL.ACCFICHEREF >0  THEN EMF.FICHENO
WHEN CLL.MODULENR=10 AND KSL.ACCFICHEREF >0  THEN EMF.FICHENO
WHEN CLL.MODULENR=61 AND CSR.ACCFICHEREF >0  THEN EMF.FICHENO Else '' END) AS [MUH.FİŞNO] ,
CLC.CODE AS [CH KODU],
CLC.DEFINITION_ AS [MÜŞTERİ ADI],
CLL.LINEEXP AS [SATIR AÇIKLAMASI],
CLF.GENEXP1 AS [İŞLEM AÇIKLAMASI],
(CASE WHEN INV.FROMKASA=1 THEN (CLL.SIGN)*CLL.AMOUNT  ELSE (1-CLL.SIGN)*CLL.AMOUNT END ) AS [BORÇ],
(CLL.SIGN*CLL.AMOUNT)  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.DATE_  BETWEEN '2018-01-01' AND '2018-12-31')
ORDER BY CLL.DATE_, CLL.MODULENR, CLL.TRANNO