#caribakiye

[sonerorhan]

Cari Bakiye Son Durum

SELECT CODE AS [CARİ KODU], DEFINITION_ [CARİ ADI], BAKIYE AS [BAKİYE],CITY AS [ŞEHİR],ADDR1,ADDR2,COUNTRY,TELNRS1,TELNRS2,FAXNR,TAXNR,EMAILADDR,TRADINGGRP,INVSENDEMAILADDR,

[B/A]=CASE WHEN B.BAKIYE < 0 THEN 'A' WHEN B.BAKIYE > 0 THEN 'B' ELSE '' END

FROM (SELECT CL.CODE, CL.DEFINITION_, CL.CITY,CL.ADDR1,CL.ADDR2,CL.COUNTRY,CL.POSTCODE,CL.TELNRS1,CL.TELNRS2, CL.FAXNR,CL.TAXNR,CL.EMAILADDR,CL.TRADINGGRP,CL.INVSENDEMAILADDR, ISNULL((SELECT(SUM (GT.DEBIT)- SUM (GT.CREDIT))

FROM LV_001_05_GNTOTCL GT

WHERE(GT.TOTTYP = 1) AND GT.CARDREF = CL.LOGICALREF), 0) BAKIYE,'' BA

FROM LG_001_CLCARD CL

WHERE CL.ACTIVE = 0) AS B

ORDER BY BAKIYE DESC

esinti kaynağı: https://semt20.home.blog/2021/08/17/logo-cari-hesap-listesi-bakiye-bakiyerd/

[sonerorhan]

Logo Sql Cari Ekstre Borç Alacak Sorgu (Bakiyesiz)

NOT: Excel'de Son sütundaki bakiyeyi aşağı doğru sürükleyince Son durum bakiyesi (-) Alacak olarak, (+) Borç olarak alınabilir.

SELECT

CAST(ROW_NUMBER() OVER(ORDER BY LG_001_05_CLFLINE.DATE_ asc) AS INT) AS SATIRNO,

CL.CODE AS CARIKODU,

CL.DEFINITION_ + ' (' + ISNULL(LG_001_PAYPLANS.DEFINITION_,'Tanımsız') + ')' AS [CARİ ADI],

LG_001_05_CLFLINE.DATE_ AS TARİH,

'İPTAL '= CASE LG_001_05_CLFLINE.CANCELLED WHEN 0 THEN '' WHEN 1 THEN 'İ' END,

CASE LG_001_05_CLFLINE.TRCODE WHEN 1 THEN 'Nakit Tahsilat' WHEN 2 THEN 'Nakit Ödeme' WHEN 3 THEN 'Borç Dekontu' WHEN 4 THEN 'Alacak Dekontu'

WHEN 5 THEN 'Virman Islemi' WHEN 6 THEN 'Kur Farkı Islemi' WHEN 12 THEN 'Özel Islem' WHEN 14 THEN 'Açılış Fişi' WHEN 20 THEN 'Gelen Havale'

WHEN 21 THEN 'Gönderilen Havale' WHEN 31 THEN 'Mal Alim Faturasi'

WHEN 33 THEN 'Toptan Satış iade Faturası' WHEN 36 THEN 'Alım Iade Faturası' WHEN 38 THEN 'Toptan Satış Faturası' WHEN 41 THEN 'Verilen Vade Farkı

Faturası' WHEN 42 THEN 'Alınan Vade Farki Faturası' WHEN 61 THEN 'Çek Girişi'

WHEN 62 THEN 'Senet Girişi' WHEN 63 THEN 'Çek Çıkış Cari Hesaba' WHEN 64 THEN 'Senet Çıkış Cari Hesaba' ELSE 'K.K Fişi' END AS ACIKLAMA,

LG_001_05_CLFLINE.TRANNO AS [BELGE NO] ,

(CASE WHEN LG_001_05_CLFLINE.SIGN = 0 THEN (LG_001_05_CLFLINE.AMOUNT ) ELSE 0 END) AS BORÇ ,

(CASE WHEN LG_001_05_CLFLINE.SIGN = 1 THEN (LG_001_05_CLFLINE.AMOUNT ) ELSE 0 END) AS ALACAK ,

((CASE WHEN LG_001_05_CLFLINE.SIGN = 0 THEN (LG_001_05_CLFLINE.AMOUNT ) ELSE 0 END )) -((CASE WHEN LG_001_05_CLFLINE.SIGN = 1 THEN (LG_001_05_CLFLINE.AMOUNT ) ELSE 0 END )) AS BAKİYE

FROM LG_001_05_CLFLINE

LEFT OUTER JOIN LG_001_CLCARD CL WITH(NOLOCK) ON CL.LOGICALREF = LG_001_05_CLFLINE.CLIENTREF

LEFT OUTER JOIN LG_001_PAYPLANS WITH(NOLOCK) ON CL.PAYMENTREF = LG_001_PAYPLANS.LOGICALREF

order by CL.DEFINITION_