Logo Cari Bakiye ve Ekstre SQL Sorgusu (Yürüyen Bakiyeli)

SELECT C.CODE AS CariKodu, C.DEFINITION_ AS Unvan,
       SUM(CASE WHEN L.SIGN = 0 THEN L.AMOUNT ELSE 0 END) AS Borc,
       SUM(CASE WHEN L.SIGN = 1 THEN L.AMOUNT ELSE 0 END) AS Alacak,
       SUM(CASE WHEN L.SIGN = 0 THEN L.AMOUNT ELSE -L.AMOUNT END) AS Bakiye
FROM LG_001_CLCARD C
JOIN LG_001_01_CLFLINE L ON L.CLIENTREF = C.LOGICALREF
WHERE L.CANCELLED = 0
GROUP BY C.CODE, C.DEFINITION_
HAVING SUM(CASE WHEN L.SIGN = 0 THEN L.AMOUNT ELSE -L.AMOUNT END) <> 0
ORDER BY Bakiye DESC

SELECT C.CODE AS CariKodu, C.DEFINITION_ AS Unvan,
       SUM(G.DEBIT) AS Borc, SUM(G.CREDIT) AS Alacak,
       SUM(G.DEBIT - G.CREDIT) AS Bakiye
FROM LV_001_01_GNTOTCL G
JOIN LG_001_CLCARD C ON C.LOGICALREF = G.CARDREF
WHERE G.TOTTYP = 1
GROUP BY C.CODE, C.DEFINITION_
ORDER BY Bakiye DESC

SELECT C.CODE AS CariKodu, C.DEFINITION_ AS Unvan,
       SUM(CASE WHEN L.SIGN = 0 THEN L.AMOUNT ELSE -L.AMOUNT END) AS Bakiye
FROM LG_001_CLCARD C
JOIN LG_001_01_CLFLINE L ON L.CLIENTREF = C.LOGICALREF
WHERE L.CANCELLED = 0
  AND L.DATE_ <= '2026-06-30'
GROUP BY C.CODE, C.DEFINITION_
HAVING SUM(CASE WHEN L.SIGN = 0 THEN L.AMOUNT ELSE -L.AMOUNT END) <> 0
ORDER BY C.CODE

SELECT L.DATE_ AS Tarih, L.TRANNO AS BelgeNo,
       CASE L.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' WHEN 14 THEN 'Devir'
            WHEN 20 THEN 'Gelen Havale' WHEN 21 THEN 'Gönderilen Havale'
            WHEN 31 THEN 'Satınalma Faturası' WHEN 34 THEN 'Alınan Hizmet Faturası'
            WHEN 36 THEN 'Satınalma İade Faturası' WHEN 37 THEN 'Perakende Satış Faturası'
            WHEN 38 THEN 'Toptan Satış Faturası' WHEN 39 THEN 'Verilen Hizmet Faturası'
            WHEN 61 THEN 'Çek Girişi' WHEN 63 THEN 'Çek Çıkışı'
            WHEN 70 THEN 'Kredi Kartı'
            ELSE CAST(L.TRCODE AS varchar(10)) END AS IslemTuru,
       L.LINEEXP AS Aciklama,
       CASE WHEN L.SIGN = 0 THEN L.AMOUNT ELSE 0 END AS Borc,
       CASE WHEN L.SIGN = 1 THEN L.AMOUNT ELSE 0 END AS Alacak,
       SUM(CASE WHEN L.SIGN = 0 THEN L.AMOUNT ELSE -L.AMOUNT END)
           OVER (ORDER BY L.DATE_, L.LOGICALREF ROWS UNBOUNDED PRECEDING) AS Bakiye
FROM LG_001_01_CLFLINE L
JOIN LG_001_CLCARD C ON C.LOGICALREF = L.CLIENTREF
WHERE C.CODE = '120.01.001' AND L.CANCELLED = 0
ORDER BY L.DATE_, L.LOGICALREF

DECLARE @Cari varchar(50) = '120.01.001',
        @Bas date = '2026-01-01', @Bit date = '2026-03-31';

SELECT CAST(NULL AS datetime) AS Tarih, 0 AS Sira, 'Devreden Bakiye' AS Aciklama,
       CAST(0 AS float) AS Borc, CAST(0 AS float) AS Alacak,
       ISNULL(SUM(CASE WHEN L.SIGN = 0 THEN L.AMOUNT ELSE -L.AMOUNT END), 0) AS Bakiye
FROM LG_001_01_CLFLINE L
JOIN LG_001_CLCARD C ON C.LOGICALREF = L.CLIENTREF
WHERE C.CODE = @Cari AND L.CANCELLED = 0 AND L.DATE_ < @Bas
UNION ALL
SELECT L.DATE_, L.LOGICALREF, L.LINEEXP,
       CASE WHEN L.SIGN = 0 THEN L.AMOUNT ELSE 0 END,
       CASE WHEN L.SIGN = 1 THEN L.AMOUNT ELSE 0 END,
       (SELECT ISNULL(SUM(CASE WHEN X.SIGN = 0 THEN X.AMOUNT ELSE -X.AMOUNT END), 0)
          FROM LG_001_01_CLFLINE X
         WHERE X.CLIENTREF = L.CLIENTREF AND X.CANCELLED = 0 AND X.DATE_ < @Bas)
       + SUM(CASE WHEN L.SIGN = 0 THEN L.AMOUNT ELSE -L.AMOUNT END)
           OVER (ORDER BY L.DATE_, L.LOGICALREF ROWS UNBOUNDED PRECEDING)
FROM LG_001_01_CLFLINE L
JOIN LG_001_CLCARD C ON C.LOGICALREF = L.CLIENTREF
WHERE C.CODE = @Cari AND L.CANCELLED = 0
  AND L.DATE_ >= @Bas AND L.DATE_ < DATEADD(day, 1, @Bit)
ORDER BY Tarih, Sira