SQL belirtilen tarihle bakiye finans muhasebe karşılaştırma kodları
/*
Cari hesaplar ile Muhasebe hesap bakiye kontrolü
Cari Hesaplar ile Muhasebe Hesapları arasında bakiye kontrolü yapmak için aşağıdaki kod kullanabilirsiniz. “F.DATE_ = @a” değerini ihtiyacınıza göre düzenleye bilirsiniz. mesal ocak ayı için MONTH(F.DATE_) = 1
@a ile ben istediğim gün şeklinde kullandım.
*/
DECLARE @a datetime
set @a = '09.30.2023'
SELECT CariKod, CariHesap, MuhasebeKod, MuhasebeHesap, ISNULL
((SELECT SUM(F.DEBIT - F.CREDIT)
FROM LG_XXX_01_EMFLINE F
WHERE F.CANCELLED = 0 AND F.DATE_ = @a AND F.TRCODE = 1 AND F.ACCOUNTCODE = A.MuhasebeKod), 0) AS MuhasebeAcilis,
ISNULL
((SELECT SUM(F.DEBIT - F.CREDIT)
FROM LG_XXX_01_EMFLINE F
WHERE F.CANCELLED = 0 AND F.DATE_ = @a AND F.ACCOUNTCODE = A.MuhasebeKod), 0) AS MuhasebeBakiye, isNULL
((SELECT SUM(CASE C.SIGN WHEN 0 THEN C.AMOUNT ELSE C.AMOUNT * - 1 END)
FROM LG_XXX_01_CLFLINE C
WHERE C.CANCELLED = 0 AND C.DATE_ = @a AND C.CLIENTREF = A.LOGICALREF AND C.TRCODE = 14), 0) AS CariAcilis, isNULL
((SELECT SUM(CASE C.SIGN WHEN 0 THEN C.AMOUNT ELSE C.AMOUNT * - 1 END)
FROM LG_XXX_01_CLFLINE C
WHERE C.CANCELLED = 0 AND C.DATE_ = @a AND C.CLIENTREF = A.LOGICALREF), 0) AS CariBakiye
FROM (SELECT LOGICALREF, CODE AS CariKod, DEFINITION_ AS CariHesap,
(SELECT MK.CODE
FROM LG_XXX_EMUHACC MK
WHERE MK.LOGICALREF =
(SELECT B.ACCOUNTREF
FROM LG_XXX_CRDACREF B
WHERE (B.TRCODE = 5) AND CK.LOGICALREF = B.CARDREF)) AS MuhasebeKod,
(SELECT MK.DEFINITION_
FROM LG_XXX_EMUHACC MK
WHERE MK.LOGICALREF =
(SELECT B.ACCOUNTREF
FROM LG_XXX_CRDACREF B
WHERE (B.TRCODE = 5) AND CK.LOGICALREF = B.CARDREF)) AS MuhasebeHesap
FROM LG_XXX_CLCARD CK) A