SQL Sorgu Cari Hesap ve Muhasebe Hesap Bakiye Karşılaştırma

ucuzler09

Yeni Üye
Katılım
21 Tem 2018
Mesajlar
7
En iyi yanıt
0
Puanları
1
Yaş
42
Konum
MELİKGAZİ
Ad Soyad
İrem YERLİ
SQL sorgusu konusunda paylaşımlarınızı rica ederim
 

ucuzler09

Yeni Üye
Katılım
21 Tem 2018
Mesajlar
7
En iyi yanıt
0
Puanları
1
Yaş
42
Konum
MELİKGAZİ
Ad Soyad
İrem YERLİ
Mevcut SQL sorgu cümlesinde tarih aralığı eklenmesi konusunda yardımlarınızı rica ederim
 

Vedat ÖZER

Logo Uzmanı
Geliştirici
Katılım
4 Haz 2018
Mesajlar
133
En iyi yanıt
0
Puanları
43
Yaş
29
Konum
Antalya / Merkez
Ad Soyad
Vedat ÖZER
Selamlar,

Tarih parametresini eklersek şöyle bir durum olacak;
Günü gününe muhasebeleştirme işlemini yapmak zorundasınız. Aksi takdirde Fatura tarihi ile muhasebe fiş tarihi tutmazsa sonuç istenen olmaya bilir.


SQL:
DECLARE @ILKTARIH VARCHAR(11)
DECLARE @SONTARIH VARCHAR(11)

SET @ILKTARIH = '01.01.2018'
SET @SONTARIH = '31.12.2018'


SELECT CariKod, CariHesap, MuhasebeKod, MuhasebeHesap, ISNULL
((SELECT CAST(SUM(F.DEBIT - F.CREDIT)AS numeric(38,2)) FROM LG_006_01_EMFLINE F WHERE F.CANCELLED = 0 AND F.TRCODE = 1
AND F.ACCOUNTCODE = A.MuhasebeKod), 0) AS MuhasebeAcilis,

ISNULL ((SELECT    CAST( SUM(F.DEBIT - F.CREDIT) AS numeric(38,2)) FROM LG_006_01_EMFLINE F WHERE F.CANCELLED = 0 
AND (F.DATE_ BETWEEN CONVERT(DATETIME,@ILKTARIH,104)
AND CONVERT(DATETIME,@ILKTARIH,104)) AND F.ACCOUNTCODE = A.MuhasebeKod), 0) AS MuhasebeBakiye,

isNULL((SELECT CAST(SUM(CASE C.SIGN WHEN 0 THEN C.AMOUNT ELSE C.AMOUNT * - 1 END) AS numeric(38,2))
FROM LG_006_01_CLFLINE C WHERE C.CANCELLED = 0 AND  C.CLIENTREF = A.LOGICALREF AND C.TRCODE = 14), 0) AS CariAcilis,

isNULL((SELECT     CAST(SUM(CASE C.SIGN WHEN 0 THEN C.AMOUNT ELSE C.AMOUNT * - 1 END) AS numeric(38,2))
FROM  LG_006_01_CLFLINE C WHERE C.CANCELLED = 0 AND
(C.DATE_ BETWEEN CONVERT(DATETIME,@ILKTARIH,104)
AND CONVERT(DATETIME,@ILKTARIH,104)) AND
C.CLIENTREF = A.LOGICALREF), 0) AS CariBakiye


FROM (SELECT LOGICALREF, CODE AS CariKod, DEFINITION_ AS CariHesap,
(SELECT MK.CODE FROM LG_006_EMUHACC MK WHERE MK.LOGICALREF =
(SELECT B.ACCOUNTREF FROM LG_006_CRDACREF B
WHERE (B.TRCODE = 5) AND CK.LOGICALREF = B.CARDREF)) AS MuhasebeKod,
(SELECT MK.DEFINITION_ FROM LG_006_EMUHACC MK WHERE MK.LOGICALREF =
(SELECT B.ACCOUNTREF FROM LG_006_CRDACREF B WHERE (B.TRCODE = 5)
AND CK.LOGICALREF = B.CARDREF)) AS MuhasebeHesap
FROM LG_006_CLCARD CK) A
 

Vedat ÖZER

Logo Uzmanı
Geliştirici
Katılım
4 Haz 2018
Mesajlar
133
En iyi yanıt
0
Puanları
43
Yaş
29
Konum
Antalya / Merkez
Ad Soyad
Vedat ÖZER
Aylık olarak kontrol sorgusunu ekliyorum.



SQL:
SELECT 

[Cari Kodu]=CLCARD.CODE,
[Cari Adı]=CLCARD.DEFINITION_,
[Muhasebe Hesap Kodu]=ISNULL((SELECT CODE FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF)),0),
[Muhasebe Hesap Adı]=ISNULL((SELECT DEFINITION_ FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF)),0),

[Ocak Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_006_01_EMUHTOT EMF WHERE MONTH_<=1 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Ocak Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_006_01_CLTOTFIL WHERE MONTH_<=1 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),

[Şubat Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_006_01_EMUHTOT EMF WHERE MONTH_=2 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Şubat Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_006_01_CLTOTFIL WHERE MONTH_=2 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),

[Mart Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_006_01_EMUHTOT EMF WHERE MONTH_=3 AND TOTTYPE <=1 AND  ACCOUNTREF IN(SELECT LOGICALREF FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Mart Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_006_01_CLTOTFIL WHERE MONTH_=3 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),

[Nisan Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_006_01_EMUHTOT EMF WHERE MONTH_=4 AND TOTTYPE <=1 AND  ACCOUNTREF IN(SELECT LOGICALREF FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Nisan Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_006_01_CLTOTFIL WHERE MONTH_=4 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),

[Mayıs Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_006_01_EMUHTOT EMF WHERE MONTH_=5 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Mayıs Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_006_01_CLTOTFIL WHERE MONTH_=5 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),

[Haziran Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_006_01_EMUHTOT EMF WHERE MONTH_=6 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Haziran Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_006_01_CLTOTFIL WHERE MONTH_=6 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),

[Temmuz Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_006_01_EMUHTOT EMF WHERE MONTH_=7 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Temmuz Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_006_01_CLTOTFIL WHERE MONTH_=7 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),

[Ağustos Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_006_01_EMUHTOT EMF WHERE MONTH_=8 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Ağustos Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_006_01_CLTOTFIL WHERE MONTH_=8 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),

[Eylül Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_006_01_EMUHTOT EMF WHERE MONTH_=9 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Eylül Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_006_01_CLTOTFIL WHERE MONTH_=9 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),

[Ekim Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_006_01_EMUHTOT EMF WHERE MONTH_=10 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Ekim Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_006_01_CLTOTFIL WHERE MONTH_=10 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),

[Kasım Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_006_01_EMUHTOT EMF WHERE MONTH_=11 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Kasım Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_006_01_CLTOTFIL WHERE MONTH_=11 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),

[Aralık Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_006_01_EMUHTOT EMF WHERE MONTH_=12 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Aralık Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_006_01_CLTOTFIL WHERE MONTH_=12 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),

[Genel Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_006_01_EMUHTOT EMF WHERE MONTH_<=12 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Genel Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_006_01_CLTOTFIL WHERE MONTH_<=12 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0)

FROM 
LG_006_CLCARD AS CLCARD
WHERE CARDTYPE<>22
 

ucuzler09

Yeni Üye
Katılım
21 Tem 2018
Mesajlar
7
En iyi yanıt
0
Puanları
1
Yaş
42
Konum
MELİKGAZİ
Ad Soyad
İrem YERLİ
Vedat Bey Çok Güzel bir çalışma oldu
Günü gününe muhasebeleştirme işlemi yapıyoruz


Fakat Carideki Son tarihli Güncel Bakiyeyi esas alıyor
ek bir işlem daha yapılıp Cari deki Belirtilen tarih sorgulaması olsa daha faydalı olacak
 

Vedat ÖZER

Logo Uzmanı
Geliştirici
Katılım
4 Haz 2018
Mesajlar
133
En iyi yanıt
0
Puanları
43
Yaş
29
Konum
Antalya / Merkez
Ad Soyad
Vedat ÖZER
Günü gününe muhasebeleştirme'de bu sorgu işe yarar hatta şöyle bir şey yapmıştım zamanında Cari hesap ekstresini listeleyip ilgili modüllerden bağlanıp tarihe bakmaksızın toplam alıp sonrasın yürüyen bakiye ile karşılaştırmıştım.

Cari bakiye'de tarih kıstası kaldırmamı istiyorsunuz.
 

Arkadasca

Yeni Üye
Katılım
20 Haz 2018
Mesajlar
88
En iyi yanıt
0
Puanları
8
Yaş
47
Konum
İstanbul
Ad Soyad
Huzeyfe Yaman
Selamlar,
Şahane bir rapor olmuş emeğinize ve paylaşımınıza sağlık... Bu iyiliği kolay kolay kimse yapmaz ;)

SQL:
AND (CODE LIKE '120%' OR CODE LIKE '320%')
Bunu ekledim daha sade ve kullanışlı oldu.

Ancak bu rapor bütün sistemi raporluyor. Sadece hareket görenleri veya sıfırdan büyük olanları nasıl gösterebiliriz?

Teşekkürler
 

snmerkan

Yeni Üye
Katılım
17 Ağu 2018
Mesajlar
12
En iyi yanıt
0
Puanları
3
Yaş
29
Konum
Ankara
Ad Soyad
Sinem Erkan
sorguyu türetilmiş tabloya alıp ilgili bakiye alanları >0 diyebilirsiniz

SQL:
SELECT TMP.MUHBAKIYE,TMP.CARIBAKIYE FROM
(
SELECT SUM(C.BAKIYE) AS CARIBAKIYE , SUM(M.BAKIYE) AS MUHBAKIYE
) TMP
WHERE TMP.MUHBAKIYE>O OR TMP.CARIBAKIYE>0
gibi
yada her subquery sonuna
SQL:
HAVING BY SUM(EMF.DEBIT-EMF.CREDIT) >0
 
Moderatör tarafında düzenlendi:

Arkadasca

Yeni Üye
Katılım
20 Haz 2018
Mesajlar
88
En iyi yanıt
0
Puanları
8
Yaş
47
Konum
İstanbul
Ad Soyad
Huzeyfe Yaman
Beceremedim :(

Aşağıdaki gibi yaptım hata verdi. Doğrusu nasıl olmalıdır?

SQL:
[Muhasebe Hesap Kodu]=ISNULL((SELECT CODE FROM LG_006_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_006_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF)),0) HAVING BY SUM(EMF.DEBIT-EMF.CREDIT) >0,
 

snmerkan

Yeni Üye
Katılım
17 Ağu 2018
Mesajlar
12
En iyi yanıt
0
Puanları
3
Yaş
29
Konum
Ankara
Ad Soyad
Sinem Erkan
kopyala yapıştırın hazin sonu :) , bence kodlardan bi çıktı alıp üzerine eğilin , daha hakim olursunuz , kel alaka bi yere koymuşsunuz veridigim örnegi.
 

Vedat ÖZER

Logo Uzmanı
Geliştirici
Katılım
4 Haz 2018
Mesajlar
133
En iyi yanıt
0
Puanları
43
Yaş
29
Konum
Antalya / Merkez
Ad Soyad
Vedat ÖZER
Sorguyu ekliyorum.

SQL:
DECLARE @ILKTARIH VARCHAR(11)
DECLARE @SONTARIH VARCHAR(11)

SET @ILKTARIH = '01.01.2006'
SET @SONTARIH = '31.12.2020'

SELECT * FROM (
SELECT CariKod, CariHesap, MuhasebeKod, MuhasebeHesap, ISNULL
((SELECT CAST(SUM(F.DEBIT - F.CREDIT)AS numeric(38,2)) FROM LG_006_01_EMFLINE F WHERE F.CANCELLED = 0 AND F.TRCODE = 1
AND F.ACCOUNTCODE = A.MuhasebeKod), 0) AS MuhasebeAcilis,
ISNULL ((SELECT    CAST( SUM(F.DEBIT - F.CREDIT) AS numeric(38,2)) FROM LG_006_01_EMFLINE F WHERE F.CANCELLED = 0
AND (F.DATE_ BETWEEN CONVERT(DATETIME,@ILKTARIH,104)
AND CONVERT(DATETIME,@ILKTARIH,104)) AND F.ACCOUNTCODE = A.MuhasebeKod), 0) AS MuhasebeBakiye,
isNULL((SELECT CAST(SUM(CASE C.SIGN WHEN 0 THEN C.AMOUNT ELSE C.AMOUNT * - 1 END) AS numeric(38,2))
FROM LG_006_01_CLFLINE C WHERE C.CANCELLED = 0 AND  C.CLIENTREF = A.LOGICALREF AND C.TRCODE = 14), 0) AS CariAcilis,
isNULL((SELECT     CAST(SUM(CASE C.SIGN WHEN 0 THEN C.AMOUNT ELSE C.AMOUNT * - 1 END) AS numeric(38,2))
FROM  LG_006_01_CLFLINE C WHERE C.CANCELLED = 0 AND
(C.DATE_ BETWEEN CONVERT(DATETIME,@ILKTARIH,104)
AND CONVERT(DATETIME,@ILKTARIH,104)) AND
C.CLIENTREF = A.LOGICALREF), 0) AS CariBakiye
FROM (SELECT LOGICALREF, CODE AS CariKod, DEFINITION_ AS CariHesap,
(SELECT MK.CODE FROM LG_006_EMUHACC MK WHERE MK.LOGICALREF =
(SELECT B.ACCOUNTREF FROM LG_006_CRDACREF B
WHERE (B.TRCODE = 5) AND CK.LOGICALREF = B.CARDREF)) AS MuhasebeKod,
(SELECT MK.DEFINITION_ FROM LG_006_EMUHACC MK WHERE MK.LOGICALREF =
(SELECT B.ACCOUNTREF FROM LG_006_CRDACREF B WHERE (B.TRCODE = 5)
AND CK.LOGICALREF = B.CARDREF)) AS MuhasebeHesap
FROM LG_006_CLCARD CK) A ) AS EXCELTURKEY
WHERE CariBakiye<>0
 

snmerkan

Yeni Üye
Katılım
17 Ağu 2018
Mesajlar
12
En iyi yanıt
0
Puanları
3
Yaş
29
Konum
Ankara
Ad Soyad
Sinem Erkan
en sondaki WHERE filtresinin sonuna AND diyerek diger istediniz alanları ekleyiniz ben ocak ve şubat yaptım

SQL:
SELECT * FROM (
SELECT
[Cari Kodu]=CLCARD.CODE,
[Cari Adı]=CLCARD.DEFINITION_,
[Muhasebe Hesap Kodu]=ISNULL((SELECT CODE FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF)),0),
[Muhasebe Hesap Adı]=ISNULL((SELECT DEFINITION_ FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF)),0),
[Ocak Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_016_01_EMUHTOT EMF WHERE MONTH_<=1 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Ocak Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_016_01_CLTOTFIL WHERE MONTH_<=1 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),
[Şubat Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_016_01_EMUHTOT EMF WHERE MONTH_=2 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Şubat Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_016_01_CLTOTFIL WHERE MONTH_=2 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),
[Mart Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_016_01_EMUHTOT EMF WHERE MONTH_=3 AND TOTTYPE <=1 AND  ACCOUNTREF IN(SELECT LOGICALREF FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Mart Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_016_01_CLTOTFIL WHERE MONTH_=3 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),
[Nisan Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_016_01_EMUHTOT EMF WHERE MONTH_=4 AND TOTTYPE <=1 AND  ACCOUNTREF IN(SELECT LOGICALREF FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Nisan Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_016_01_CLTOTFIL WHERE MONTH_=4 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),
[Mayıs Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_016_01_EMUHTOT EMF WHERE MONTH_=5 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Mayıs Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_016_01_CLTOTFIL WHERE MONTH_=5 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),
[Haziran Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_016_01_EMUHTOT EMF WHERE MONTH_=6 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Haziran Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_016_01_CLTOTFIL WHERE MONTH_=6 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),
[Temmuz Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_016_01_EMUHTOT EMF WHERE MONTH_=7 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Temmuz Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_016_01_CLTOTFIL WHERE MONTH_=7 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),
[Ağustos Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_016_01_EMUHTOT EMF WHERE MONTH_=8 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Ağustos Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_016_01_CLTOTFIL WHERE MONTH_=8 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),
[Eylül Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_016_01_EMUHTOT EMF WHERE MONTH_=9 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Eylül Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_016_01_CLTOTFIL WHERE MONTH_=9 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),
[Ekim Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_016_01_EMUHTOT EMF WHERE MONTH_=10 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Ekim Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_016_01_CLTOTFIL WHERE MONTH_=10 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),
[Kasım Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_016_01_EMUHTOT EMF WHERE MONTH_=11 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Kasım Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_016_01_CLTOTFIL WHERE MONTH_=11 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),
[Aralık Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_016_01_EMUHTOT EMF WHERE MONTH_=12 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Aralık Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_016_01_CLTOTFIL WHERE MONTH_=12 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0),
[Genel Muhasebe]=ISNULL((SELECT SUM(EMF.DEBIT-EMF.CREDIT) FROM LV_016_01_EMUHTOT EMF WHERE MONTH_<=12 AND TOTTYPE <=1 AND ACCOUNTREF IN(SELECT LOGICALREF FROM LG_016_EMUHACC WHERE LOGICALREF=(SELECT ACCOUNTREF FROM LG_016_CRDACREF WHERE TRCODE=5 AND  CLCARD.LOGICALREF=CARDREF))),0),
[Genel Cari]=ISNULL((SELECT SUM(DEBIT-CREDIT) FROM LV_016_01_CLTOTFIL WHERE MONTH_<=12 AND TOTTYP=1 AND  CLCARD.LOGICALREF=CARDREF),0)
FROM
LG_016_CLCARD AS CLCARD
WHERE CARDTYPE<>22 ) TMP
WHERE TMP.[Ocak Muhasebe]<>0 AND TMP.[Ocak Cari]<>0 AND TMP.[Şubat Muhasebe]<>0 AND TMP.[Şubat Cari]<>0
 
Moderatör tarafında düzenlendi:
Üst Alt