Çözüldü Portföydeki çek durumunda ciro eden bilgisi

Sub Cekler()
Call degisken: Call baglan: Call tertemiz
S = "SELECT SETDATE 'GİRİŞ TARİHİ', "
S = S & " CASE DOC    WHEN 1 THEN 'Çek Girişi' "
S = S & "         WHEN 2 THEN 'Senet Girişi' "
S = S & "         WHEN 3 THEN 'Çek Çıkış (Cari Hesaba)' "
S = S & "         WHEN 4 THEN 'Senet Çıkış (Cari Hesaba)' "
S = S & "         WHEN 5 THEN 'Çek Çıkış (Banka Tahsil)' "
S = S & "         WHEN 6 THEN 'Senet Çıkış (Banka Tahsil)' "
S = S & "         WHEN 7 THEN 'Çek Çıkış (Banka Teminat)' "
S = S & "         WHEN 8 THEN 'Senet Çıkış (Banka Teminat)' "
S = S & "         WHEN 9 THEN 'İşlem Bordrosu(Müşteri Çeki)' "
S = S & "         WHEN 10 THEN 'İşlem bordrosu (Müşteri Senedi)' "
S = S & "         WHEN 11 THEN 'İşlem Bordrosu (Kendi Çekimiz)' "
S = S & "         WHEN 12 THEN 'İşlem Bordrosu (Borç Senedimiz)' Else 'Ne Oldugu Belirsiz' END 'İŞLEM TÜRÜ', "
S = S & " Case CURRSTAT   WHEN 1 THEN 'Portföyde' "
S = S & "         WHEN 2 THEN 'Ciro Edildi' "
S = S & "         WHEN 3 THEN 'Teminata Verildi' "
S = S & "         WHEN 4 THEN 'Tahsile Verildi' "
S = S & "         WHEN 5 THEN 'Protestolu Tahsile Verildi' "
S = S & "         WHEN 6 THEN 'İade Edildi' "
S = S & "         WHEN 7 THEN 'Protesto Edildi' "
S = S & "         WHEN 8 THEN 'Tahsil Edildi' "
S = S & "         WHEN 9 THEN 'Kendi Çekimiz' "
S = S & "         WHEN 10 THEN 'Borç Senedimiz' "
S = S & "         WHEN 11 THEN 'Karşılığı Yok' "
S = S & "         WHEN 12 THEN 'Tahsil Edilemiyor' Else 'Ne Oldugu Belirsiz' END 'DURUMU', "
S = S & " PORTFOYNO 'PORTFÖY NO', NEWSERINO 'SERİ NO',DueDate 'VADE',BANKNAME 'BANKA ADI', OWING 'BORÇLU',AMOUNT 'TUTAR' "
S = S & " FROM LG_" & Firma & "_" & Dönem & "_CSCARD "
S = S & " WHERE CURRSTAT IN (1) AND DOC IN (1,2) " 'AND DUEDATE BETWEEN '" & tarih1 & "' AND '" & tarih2 & "' OR SETDATE BETWEEN '" & tarih1 & "' AND '" & tarih2 & "' "
S = S & " ORDER BY DUEDATE "
rs.Open S, con
Cells(7, 1).CopyFromRecordset rs
Call kapat
End Sub

SELECT
convert(datetime,SETDATE,104)AS TARİH, 
convert(datetime,DUEDATE,104) AS VADE,
YEAR(DUEDATE)AS VADE_YIL,
MONTH(DUEDATE)AS VADE_AY,
(SELECT TOP (1) CL.DEFINITION_ FROM LG_006_01_CSTRANS AS CST INNER JOIN LG_006_CLCARD AS CL ON CST.CARDREF = CL.LOGICALREF WHERE (CST.CSREF = LGMAIN.LOGICALREF) ORDER BY CST.LOGICALREF) AS [CARİ ÜNVANI],
 OWING AS BORÇLU,
CASE WHEN LGMAIN.CURRSTAT = 1 THEN 'Portföyde' WHEN LGMAIN.CURRSTAT = 2 THEN 'Ciro Edildi' WHEN LGMAIN.CURRSTAT = 3 THEN 'Teminata Verildi' WHEN LGMAIN.CURRSTAT = 4 THEN 'Tahsile Verildi'
WHEN LGMAIN.CURRSTAT = 5 THEN 'Tahsile Verildi(Protestolu)' WHEN LGMAIN.CURRSTAT = 6 THEN 'İade Edildi' WHEN LGMAIN.CURRSTAT = 7 THEN 'Protesto Edildi' WHEN LGMAIN.CURRSTAT = 8 THEN 'Tahsil Edildi'
WHEN LGMAIN.CURRSTAT = 9 THEN 'Verilen Çek'when  LGMAIN.CURRSTAT = 9 then 'Borç Senedimiz' WHEN LGMAIN.CURRSTAT = 10 THEN 'Borç Senedimiz'WHEN LGMAIN.CURRSTAT = 11 THEN 'Karşılığı Yok' WHEN LGMAIN.CURRSTAT =
12 THEN 'Tahsil Edilemiyor' WHEN LGMAIN.CURRSTAT = 14 THEN 'Portföyde Protestolu' END AS DURUM,
CASE WHEN LGMAIN.DOC = 3 THEN 'Kendi Çekimiz' WHEN LGMAIN.DOC = 2 THEN 'Müşteri Seneti'WHEN LGMAIN.DOC = 4 THEN 'Kendi Senedimiz' WHEN LGMAIN.DOC = 1 THEN 'Müşteri Çeki' END AS TUR,
(SELECT CODE FROM  LG_006_BANKACC AS BN WHERE   (LGMAIN.OURBANKREF = LOGICALREF)) AS [BANKA KODU], BANKNAME AS [BANKA AÇIKLAMASI], BNBRANCHNO AS [ŞUBE KODU], BNACCOUNTNO AS [HESAP NO],
 IBAN AS İBAN,  NEWSERINO AS SERINO, PORTFOYNO AS [PORTFÖY NO], TRNET AS TUTAR,'DÖVİZ TÜRÜ'=CASE WHEN LGMAIN.TRCURR IN (0,160) THEN 'TL' WHEN LGMAIN.TRCURR=1 THEN 'USD' WHEN LGMAIN.TRCURR=20
  THEN 'EUR' ELSE '' END,
[DEVİR]     = CASE LGMAIN.DEVIR  WHEN 0 THEN 'Hayir' ELSE 'Evet' END,
CASE WHEN LGMAIN.CURRSTAT = 1 THEN '' ELSE
(SELECT  TOP 1 CASE WHEN CST.CARDMD = 5 THEN(SELECT CL.DEFINITION_
FROM LG_006_CLCARD CL
WHERE CST.CARDREF = CL.LOGICALREF) WHEN CST.CARDMD = 7 THEN
(SELECT BNK.DEFINITION_
FROM LG_006_BANKACC BNK
WHERE CST.CARDREF = BNK.LOGICALREF) END
FROM LG_006_01_CSTRANS CST
WHERE CST.CSREF = LGMAIN.LOGICALREF
ORDER BY CST.LOGICALREF DESC) END AS [VERİLEN (CARİ/BANKA)], CASE WHEN LGMAIN.CURRSTAT = 1 THEN '' ELSE
(SELECT TOP 1 CASE WHEN CST.CARDMD = 5 THEN
(SELECT CL.CODE
FROM LG_006_CLCARD CL
WHERE CST.CARDREF = CL.LOGICALREF) WHEN CST.CARDMD = 7 THEN
(SELECT BNK.CODE
FROM LG_006_BANKACC BNK
WHERE CST.CARDREF = BNK.LOGICALREF) END
FROM LG_006_01_CSTRANS CST
WHERE CST.CSREF = LGMAIN.LOGICALREF
ORDER BY CST.LOGICALREF DESC) END AS [VERİLEN (CARİ/BANKA) KODU], BRANCH AS İŞYERİ,
(SELECT NAME
FROM L_CAPIUSER AS U
WHERE(LGMAIN.CAPIBLOCK_CREATEDBY = NR)) AS [KAYIT BİLGİSİ],
convert(datetime,CAPIBLOCK_CREADEDDATE,104) AS [KAYIT TARİHİ],
(SELECT NAME
FROM L_CAPIUSER AS U
WHERE(LGMAIN.CAPIBLOCK_MODIFIEDBY = NR)) AS [DEĞİŞTİRME BİLGİSİ], convert(datetime,CAPIBLOCK_MODIFIEDDATE,104) AS [DEĞİŞTİRME TARİHİ]
FROM LG_006_01_CSCARD AS LGMAIN
ORDER BY    DUEDATE ASC

SELECT * FROM (

SELECT

CASE

WHEN LGMAIN.CURRSTAT = 2 THEN 'Ciro Edildi'  ELSE '' END DURUM

FROM LG_006_CSCARD) AS EXCELTURKEY

WHERE DURUM='Ciro Edildi'