Sorgu Boş Geliyor

[cousen]

Sorgu tamam fakat eksikler tahsilat türlerini nasıl eklemem gerekiyor birde toplam bakiye'yi göstermiyor ?

Birşey daha bilmek istiyorum sorgu'da cari kod kısmı sıralı değil karışık geliyor bunun sebebi ve çözümü nedir ?

[cousen]

Bakiye'yi de ekledim tek sıkıntı son tahsilatve son satış tarihi için bütün tahsilat ve satış türlerinin olması

Birde cari kodların sıralı değilde karışık gelmesi



SELECT  CL.CODE AS CHCODE,   CL.DEFINITION_ AS 'Unvan', SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '0' THEN LV_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Acilis_Borc,
                      SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '0' THEN LV_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Acilis_Alacak,
                      SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '1' THEN LV_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Ocak_Borc,
                      SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '1' THEN LV_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Ocak_Alacak,
                      SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '2' THEN LV_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Subat_Borc,
                      SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '2' THEN LV_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Subat_Alacak,
            SUM(dbo.LV_016_01_CLTOTFIL.DEBIT) AS TOPLAMBORÇ,
            SUM(dbo.LV_016_01_CLTOTFIL.CREDIT) AS TOPLAMALACAK,
                      SUM(dbo.LV_016_01_CLTOTFIL.DEBIT-CREDIT) AS BAKIYE,
            CONVERT(NVARCHAR(20), (SELECT TOP 1 DATE_ FROM LG_016_01_STLINE AS STL WHERE TRCODE IN (7,8) AND STL.CLIENTREF=CL.LOGICALREF ORDER BY DATE_ DESC),105) AS [SON SATIS TARIHI],
            CONVERT(NVARCHAR(20), (SELECT TOP 1  CLF.DATE_  FROM  LG_016_01_CLFICHE AS CLF WHERE CLF.CLCARDREF = CL.LOGICALREF ORDER BY CLF.DATE_ DESC),105) AS  [SON TAHSILAT TARİHİ]
               


FROM         dbo.LV_016_01_CLTOTFIL INNER JOIN
                      dbo.LG_016_CLCARD AS CL ON dbo.LV_016_01_CLTOTFIL.CARDREF = CL.LOGICALREF
WHERE     (CL.ACTIVE = 0)
GROUP BY CL.DEFINITION_,CL.CODE,CL.LOGICALREF

[Teknik21]

En alt satıra ;

ORDER BY CHCODE ASC

[cousen]

En alt satıra ;

ORDER BY CHCODE ASC

Teşekkürler tamamdır. Tek eksik son tahsilat ve son satış tarihinde bütün hareketleri tarayıp son B yada A ' ya göre tarihi vermemesi.

[Teknik21]

Şöle ki ;

Kodda 7 [prakende satış] /  8  [Toptan şatış]   - WHERE TRCODE IN (7,8) AND- silersen tüm şatış türleri gelir...

Kod: [Seç]

CONVERT(NVARCHAR(20), (SELECT TOP 1 DATE_ FROM LG_016_01_STLINE AS STL WHERE TRCODE IN (7,8) AND STL.CLIENTREF=CL.LOGICALREF ORDER BY DATE_ DESC),105) AS [SON SATIS TARIHI],

[cousen]

Tamam peki tüm tahsilat türleri için ne yapmam gerekiyor. İncelediğim kadarı ile sadece k.kartı tahsilatlarına göre tarih veriyor ben hepsine göre istiyorum.

[Teknik21]

Tüm hareketleri alan sorgu ;

Kod: [Seç]

CONVERT(NVARCHAR(20), (SELECT TOP 1  CLF.DATE_  FROM  LG_016_01_CLFLINE AS CLF WHERE CLF.CLIENTREF = CL.LOGICALREF ORDER BY CLF.DATE_ DESC),105) AS  [SON TAHSILAT TARİHİ]

[cousen]

Son hali bu fakat son tahsilat tarihlerinde ve son satış tarihleri sıkıntı var dogru gelmiyor.

SELECT CL.CODE AS CHCODE, CL.DEFINITION_ AS 'Unvan', SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '0' THEN LV_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Acilis_Borc,
SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '0' THEN LV_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Acilis_Alacak,
SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '1' THEN LV_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Ocak_Borc,
SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '1' THEN LV_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Ocak_Alacak,
SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '2' THEN LV_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Subat_Borc,
SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '2' THEN LV_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Subat_Alacak,
SUM(dbo.LV_016_01_CLTOTFIL.DEBIT) AS Toplam Borc,
SUM(dbo.LV_016_01_CLTOTFIL.CREDIT) AS Toplam Alacak,
SUM(dbo.LV_016_01_CLTOTFIL.DEBIT-CREDIT) AS Bakiye,
CONVERT(NVARCHAR(20), (SELECT TOP 1 DATE_ FROM LG_016_01_STLINE AS STL WHERE STL.CLIENTREF=CL.LOGICALREF ORDER BY DATE_ DESC),105) AS [Son Satıs Tarihi],
CONVERT(NVARCHAR(20), (SELECT TOP 1 CLF.DATE_ FROM LG_016_01_CLFICHE AS CLF WHERE CLF.CLCARDREF = CL.LOGICALREF ORDER BY CLF.DATE_ DESC),105) AS [Son Tahsilat Tarihi]



FROM dbo.LV_016_01_CLTOTFIL INNER JOIN
dbo.LG_016_CLCARD AS CL ON dbo.LV_016_01_CLTOTFIL.CARDREF = CL.LOGICALREF
WHERE (CL.ACTIVE = 0)
GROUP BY CL.DEFINITION_,CL.CODE,CL.LOGICALREF
ORDER BY CHCODE ASC

[Teknik21]

Son hali ;

Kod: [Seç]

SELECT  CL.CODE AS CHCODE,   CL.DEFINITION_ AS 'Unvan', SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '0' THEN LV_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Acilis_Borc,
                      SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '0' THEN LV_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Acilis_Alacak,
                      SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '1' THEN LV_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Ocak_Borc,
                      SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '1' THEN LV_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Ocak_Alacak,
                      SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '2' THEN LV_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Subat_Borc,
                      SUM(CASE WHEN LV_016_01_CLTOTFIL.MONTH_ = '2' THEN LV_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Subat_Alacak,
            SUM(dbo.LV_016_01_CLTOTFIL.DEBIT) AS TOPLAMBORÇ,
            SUM(dbo.LV_016_01_CLTOTFIL.CREDIT) AS TOPLAMALACAK,
                      SUM(dbo.LV_016_01_CLTOTFIL.DEBIT-CREDIT) AS BAKIYE,
            CONVERT(NVARCHAR(20), (SELECT TOP 1 DATE_ FROM LG_016_01_STLINE 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  LG_016_01_CLFLINE AS CLF WHERE CLF.CLIENTREF = CL.LOGICALREF ORDER BY CLF.DATE_ DESC),105) AS  [SON TAHSILAT TARİHİ]
               


FROM         dbo.LV_016_01_CLTOTFIL INNER JOIN
                      dbo.LG_016_CLCARD AS CL ON dbo.LV_016_01_CLTOTFIL.CARDREF = CL.LOGICALREF
WHERE     (CL.ACTIVE = 0)
GROUP BY CL.DEFINITION_,CL.CODE,CL.LOGICALREF
ORDER BY CHCODE ASC




[cousen]

Herşey dogru fakat dediğim gibi tarihler dogru gelmiyor ?

[haskan6961]

Kod: [Seç]

SELECT *,
FORMAT(TMP.TOPLAMALACAK-TMP.TOPLAMBORÇ,'C','TR-tr') AS KK
 FROM
(SELECT  CL.CODE AS CHCODE,   CL.DEFINITION_ AS 'Unvan',
SUM(CASE WHEN LG_016_01_CLTOTFIL.MONTH_ = '0' THEN LG_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Acilis_Borc,
SUM(CASE WHEN LG_016_01_CLTOTFIL.MONTH_ = '0' THEN LG_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Acilis_Alacak,
SUM(CASE WHEN LG_016_01_CLTOTFIL.MONTH_ = '1' THEN LG_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Ocak_Borc,
SUM(CASE WHEN LG_016_01_CLTOTFIL.MONTH_ = '1' THEN LG_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Ocak_Alacak,
SUM(CASE WHEN LG_016_01_CLTOTFIL.MONTH_ = '2' THEN LG_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Subat_Borc,
SUM(CASE WHEN LG_016_01_CLTOTFIL.MONTH_ = '2' THEN LG_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Subat_Alacak,
SUM(LG_016_01_CLTOTFIL.DEBIT) AS TOPLAMBORÇ,
SUM(LG_016_01_CLTOTFIL.CREDIT) AS TOPLAMALACAK
,CONVERT(NVARCHAR(20), (SELECT TOP 1 DATE_ FROM LG_016_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  LG_016_01_CLFLINE AS CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND MODULENR<>4 ORDER BY CLF.DATE_ DESC),105) AS  [SON TAHSILAT TARİHİ]
FROM  LG_016_01_CLTOTFIL INNER JOIN
      LG_016_CLCARD AS CL ON LG_016_01_CLTOTFIL.CARDREF = CL.LOGICALREF
WHERE     (CL.ACTIVE = 0) AND TOTTYP=1
GROUP BY CL.DEFINITION_,CL.CODE,CL.LOGICALREF
)TMP

[cousen]

Kod: [Seç]

SELECT *,
FORMAT(TMP.TOPLAMALACAK-TMP.TOPLAMBORÇ,'C','TR-tr') AS KK
 FROM
(SELECT  CL.CODE AS CHCODE,   CL.DEFINITION_ AS 'Unvan',
SUM(CASE WHEN LG_016_01_CLTOTFIL.MONTH_ = '0' THEN LG_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Acilis_Borc,
SUM(CASE WHEN LG_016_01_CLTOTFIL.MONTH_ = '0' THEN LG_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Acilis_Alacak,
SUM(CASE WHEN LG_016_01_CLTOTFIL.MONTH_ = '1' THEN LG_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Ocak_Borc,
SUM(CASE WHEN LG_016_01_CLTOTFIL.MONTH_ = '1' THEN LG_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Ocak_Alacak,
SUM(CASE WHEN LG_016_01_CLTOTFIL.MONTH_ = '2' THEN LG_016_01_CLTOTFIL.DEBIT ELSE 0 END) AS Subat_Borc,
SUM(CASE WHEN LG_016_01_CLTOTFIL.MONTH_ = '2' THEN LG_016_01_CLTOTFIL.CREDIT ELSE 0 END) AS Subat_Alacak,
SUM(LG_016_01_CLTOTFIL.DEBIT) AS TOPLAMBORÇ,
SUM(LG_016_01_CLTOTFIL.CREDIT) AS TOPLAMALACAK
,CONVERT(NVARCHAR(20), (SELECT TOP 1 DATE_ FROM LG_016_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  LG_016_01_CLFLINE AS CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND MODULENR<>4 ORDER BY CLF.DATE_ DESC),105) AS  [SON TAHSILAT TARİHİ]
FROM  LG_016_01_CLTOTFIL INNER JOIN
      LG_016_CLCARD AS CL ON LG_016_01_CLTOTFIL.CARDREF = CL.LOGICALREF
WHERE     (CL.ACTIVE = 0) AND TOTTYP=1
GROUP BY CL.DEFINITION_,CL.CODE,CL.LOGICALREF
)TMP

Şimdi oldu Teşekkür ederim herkese yardımlarınız için