Merhabalar,
Logo Tiger 3 kullanıyorum Aylık cari hesapların bakiyelerinin aşağıdaki gibi ay ay görebileceğim pivot tablo şeklinde bir sorguya ihtiyacım var yardımcı olabilir misiniz ?
SELECT CHCODE,Unvan,Acilis_Borc,Acilis_Alacak,(Acilis_Borc-Acilis_Alacak) [Acılıs Bakiye],
Ocak_Borc,Ocak_Alacak,(Ocak_Borc-Ocak_Alacak) as [Ocak Bakiye],
Subat_Borc,Subat_Alacak,(Subat_Borc-Subat_Alacak) as [Subat Bakiye],
Mart_Borc,Mart_Alacak,(Mart_Borc-Mart_Alacak) as [Mart Bakiye],
Nisan_Borc,Nisan_Alacak,(Nisan_Borc-Nisan_Alacak) as [Nisan Bakiye],
Mayıs_Borc,Mayıs_Alacak,(Mayıs_Borc-Mayıs_Alacak) as [Mayıs Bakiye],
Haziran_Borc,Haziran_Alacak,(Haziran_Borc-Haziran_Alacak) as [Haziran Bakiye],
Temmuz_Borc,Temmuz_Alacak,(Temmuz_Borc-Temmuz_Alacak) as [Temmuz Bakiye],
Ağustos_Borc,Ağustos_Alacak,(Ağustos_Borc-Ağustos_Alacak) as [Ağustos Bakiye],
Eylül_Borc,Eylül_Alacak,(Eylül_Borc-Eylül_Alacak) as [Eylül Bakiye],
Ekim_Borc,Ekim_Alacak,(Ekim_Borc-Ekim_Alacak) as [Ekim Bakiye],
TOPLAMBORÇ,TOPLAMALACAK,(TOPLAMBORÇ-TOPLAMALACAK) AS TOPLAMBAKİYE FROM (
SELECT CL.CODE AS CHCODE, CL.DEFINITION_ AS 'Unvan',
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '0' THEN LV_041_01_CLTOTFIL.DEBIT ELSE 0 END) AS Acilis_Borc,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '0' THEN LV_041_01_CLTOTFIL.CREDIT ELSE 0 END) AS Acilis_Alacak,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '1' THEN LV_041_01_CLTOTFIL.DEBIT ELSE 0 END) AS Ocak_Borc,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '1' THEN LV_041_01_CLTOTFIL.CREDIT ELSE 0 END) AS Ocak_Alacak,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '2' THEN LV_041_01_CLTOTFIL.DEBIT ELSE 0 END) AS Subat_Borc,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '2' THEN LV_041_01_CLTOTFIL.CREDIT ELSE 0 END) AS Subat_Alacak,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '3' THEN LV_041_01_CLTOTFIL.DEBIT ELSE 0 END) AS Mart_Borc,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '3' THEN LV_041_01_CLTOTFIL.CREDIT ELSE 0 END) AS Mart_Alacak,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '4' THEN LV_041_01_CLTOTFIL.DEBIT ELSE 0 END) AS Nisan_Borc,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '4' THEN LV_041_01_CLTOTFIL.CREDIT ELSE 0 END) AS Nisan_Alacak,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '5' THEN LV_041_01_CLTOTFIL.DEBIT ELSE 0 END) AS Mayıs_Borc,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '5' THEN LV_041_01_CLTOTFIL.CREDIT ELSE 0 END) AS Mayıs_Alacak,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '6' THEN LV_041_01_CLTOTFIL.DEBIT ELSE 0 END) AS Haziran_Borc,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '6' THEN LV_041_01_CLTOTFIL.CREDIT ELSE 0 END) AS Haziran_Alacak,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '7' THEN LV_041_01_CLTOTFIL.DEBIT ELSE 0 END) AS Temmuz_Borc,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '7' THEN LV_041_01_CLTOTFIL.CREDIT ELSE 0 END) AS Temmuz_Alacak,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '8' THEN LV_041_01_CLTOTFIL.DEBIT ELSE 0 END) AS Ağustos_Borc,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '8' THEN LV_041_01_CLTOTFIL.CREDIT ELSE 0 END) AS Ağustos_Alacak,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '9' THEN LV_041_01_CLTOTFIL.DEBIT ELSE 0 END) AS Eylül_Borc,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '9' THEN LV_041_01_CLTOTFIL.CREDIT ELSE 0 END) AS Eylül_Alacak,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '10' THEN LV_041_01_CLTOTFIL.DEBIT ELSE 0 END) AS Ekim_Borc,
SUM(CASE WHEN LV_041_01_CLTOTFIL.MONTH_ = '10' THEN LV_041_01_CLTOTFIL.CREDIT ELSE 0 END) AS Ekim_Alacak,
SUM(dbo.LV_041_01_CLTOTFIL.DEBIT) AS TOPLAMBORÇ,
SUM(dbo.LV_041_01_CLTOTFIL.CREDIT) AS TOPLAMALACAK,
SUM(dbo.LV_041_01_CLTOTFIL.DEBIT-CREDIT) AS BAKIYE,
CONVERT(NVARCHAR(20), (SELECT TOP 1 DATE_ FROM LG_041_01_INVOICE AS STL WHERE STL.CLIENTREF=CL.LOGICALREF ORDER BY DATE_ DESC),105) AS [SON SATIS TARIHI],
CONVERT(NVARCHAR(20), (SELECT TOP 1 CLF.DATE_ FROM LV_041_01_CLFLINE AS CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND MODULENR<>4 ORDER BY CLF.DATE_ DESC),105) AS [SON TAHSILAT TARİHİ]
FROM LV_041_01_CLTOTFIL INNER JOIN
LV_041_CLCARD AS CL ON LV_041_01_CLTOTFIL.CARDREF = CL.LOGICALREF
WHERE (CL.ACTIVE = 0) AND TOTTYP=1 AND CL.CODE NOT LIKE ('PERS%') AND CL.CODE NOT LIKE ('320%') AND CL.CODE NOT LIKE ('77%') AND CL.CODE NOT LIKE ('33%') AND CL.CODE NOT LIKE ('500%')
GROUP BY CL.DEFINITION_,CL.CODE,CL.LOGICALREF
) AS EXCELVB
