38 lines
1.8 KiB
Transact-SQL
38 lines
1.8 KiB
Transact-SQL
/*
|
||
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 = '11.06.2014'
|
||
SELECT CariKod, CariHesap, MuhasebeKod, MuhasebeHesap, ISNULL
|
||
((SELECT SUM(F.DEBIT - F.CREDIT)
|
||
FROM LG_001_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_001_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_001_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_001_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_001_EMUHACC MK
|
||
WHERE MK.LOGICALREF =
|
||
(SELECT B.ACCOUNTREF
|
||
FROM LG_001_CRDACREF B
|
||
WHERE (B.TRCODE = 5) AND CK.LOGICALREF = B.CARDREF)) AS MuhasebeKod,
|
||
(SELECT MK.DEFINITION_
|
||
FROM LG_001_EMUHACC MK
|
||
WHERE MK.LOGICALREF =
|
||
(SELECT B.ACCOUNTREF
|
||
FROM LG_001_CRDACREF B
|
||
WHERE (B.TRCODE = 5) AND CK.LOGICALREF = B.CARDREF)) AS MuhasebeHesap
|
||
FROM LG_001_CLCARD CK) A |