• Merhaba Ziyaretçi,
    Microsoft 365 Uygulamaları ile ilgili yeni haberler, dikkat çekici konular, ilgi ile takip edeceğiniz yazılar için.

    Abone Olun
  • ESTE - Microsoft Office Eğitimleri

    Yeni yıl Microsoft Office Eğitim planlarınız için bütçenizi oluşturmadan önce ESTE eğitim kalitesi ile tanışın. 🙌
    Kullanıcıların ihtiyacı olan yazılı materyal, dosya ve video kaynağı desteğimiz ile tüm ofis çalışanlarının iş süreçlerini rahatlatacak eğitimler planlayın. 🎯
    Microsoft Office eğitimlerimiz hakkında detaylı bilgi için bize ulaşın.

    👉 Microsoft Office Eğitim Talebi

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

Murat OSMA

Yönetici
Site Yöneticisi
Katılım
25 May 2018
Mesajlar
1,504
En iyi yanıt
13
Puanları
113
Konum
İstanbul
Web sitesi
excelarsivi.com
Ad Soyad
Murat OSMA
Office Vers.
Office 365 TR+EN
Logo'da Cari Hesap ve Muhasebe Hesap Bakiye Karşılaştırması için bu sorguyu kullanabilirsiniz.

[HIDEREPLYTHANKS]
SQL:
SELECT CariKod, CariHesap, MuhasebeKod, MuhasebeHesap, ISNULL
((SELECT SUM(F.DEBIT - F.CREDIT)
FROM LG_607_01_EMFLINE F WHERE F.CANCELLED = 0 AND F.TRCODE = 1 AND
F.ACCOUNTCODE = A.MuhasebeKod), 0) AS MuhasebeAcilis,
ISNULL ((SELECT     SUM(F.DEBIT - F.CREDIT) FROM LG_607_01_EMFLINE F
WHERE F.CANCELLED = 0 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_607_01_CLFLINE C WHERE C.CANCELLED = 0 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_607_01_CLFLINE C WHERE C.CANCELLED = 0 AND C.CLIENTREF = A.LOGICALREF), 0) AS CariBakiye
FROM (SELECT LOGICALREF, CODE AS CariKod, DEFINITION_ AS CariHesap,
(SELECT MK.CODE FROM LG_607_EMUHACC MK WHERE MK.LOGICALREF =
(SELECT B.ACCOUNTREF FROM LG_607_CRDACREF B
WHERE (B.TRCODE = 5) AND CK.LOGICALREF = B.CARDREF)) AS MuhasebeKod,
(SELECT MK.DEFINITION_ FROM LG_607_EMUHACC MK WHERE MK.LOGICALREF =
(SELECT B.ACCOUNTREF FROM LG_607_CRDACREF B WHERE (B.TRCODE = 5)
AND CK.LOGICALREF = B.CARDREF)) AS MuhasebeHesap
FROM LG_607_CLCARD CK) A
[/HIDEREPLYTHANKS]


Sonuç Tablo

carimuhasebebakiyekarsilastirma-min.png
 
Son düzenleme:

ucuzler09

Yeni Üye
Katılım
21 Tem 2018
Mesajlar
7
En iyi yanıt
0
Puanları
1
Yaş
47
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ş
47
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
288
En iyi yanıt
4
Puanları
63
Yaş
33
Konum
Antalya / Merkez
Ad Soyad
Vedat ÖZER
Office Vers.
2019
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
288
En iyi yanıt
4
Puanları
63
Yaş
33
Konum
Antalya / Merkez
Ad Soyad
Vedat ÖZER
Office Vers.
2019
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ş
47
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
288
En iyi yanıt
4
Puanları
63
Yaş
33
Konum
Antalya / Merkez
Ad Soyad
Vedat ÖZER
Office Vers.
2019
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.
 

Vedat ÖZER

Logo Uzmanı
Geliştirici
Katılım
4 Haz 2018
Mesajlar
288
En iyi yanıt
4
Puanları
63
Yaş
33
Konum
Antalya / Merkez
Ad Soyad
Vedat ÖZER
Office Vers.
2019
Firmada ve dönem bilgilerini doğru girdikten sonra doğru çalışır.
 

Rahle

Yeni Üye
Katılım
20 Haz 2018
Mesajlar
137
En iyi yanıt
0
Puanları
18
Yaş
51
Konum
İstanbul
Ad Soyad
Huzeyfe Yaman
Office Vers.
Office 2010
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ş
34
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:

Rahle

Yeni Üye
Katılım
20 Haz 2018
Mesajlar
137
En iyi yanıt
0
Puanları
18
Yaş
51
Konum
İstanbul
Ad Soyad
Huzeyfe Yaman
Office Vers.
Office 2010
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ş
34
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
288
En iyi yanıt
4
Puanları
63
Yaş
33
Konum
Antalya / Merkez
Ad Soyad
Vedat ÖZER
Office Vers.
2019
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ş
34
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:

Rahle

Yeni Üye
Katılım
20 Haz 2018
Mesajlar
137
En iyi yanıt
0
Puanları
18
Yaş
51
Konum
İstanbul
Ad Soyad
Huzeyfe Yaman
Office Vers.
Office 2010
Tamamdır. Teşekkürler
 
Üst Alt