SELECT TOP (100) PERCENT CL.DEFINITION_, MAX(SUB.BAKIYE) AS BAKIYE, MAX(SUB.SONSATISTARIHI) AS [SON SATIS TARIHI], MAX(SUB.SONSATISTUTARI) AS [SON SATIS TUTARI], MAX(SLS.CODE) AS ELEMAN,
MAX(SUB.SONTAHSILATTARIHI) AS [SON TAHSILAT TARIHI], MAX(SUB.SONTAHSILATTUTARI) AS [SON TAHSILAT TUTARI], dbo.fn_trcode('Clfline', MAX(SUB.SONTAHSILATTURU), 0, 0)
AS [SON TAHSILAT TURU]
FROM (SELECT CLIENTREF AS ALICI, SUM(CASE WHEN SIGN = 0 THEN AMOUNT ELSE - AMOUNT END) AS BAKIYE, NULL AS SONSATISTARIHI, NULL AS SONSATISTUTARI, NULL AS SONSATICI, NULL
AS SONTAHSILATTARIHI, NULL AS SONTAHSILATTUTARI, NULL AS SONTAHSILATTURU
FROM dbo.FS_CARI AS F
GROUP BY CLIENTREF
UNION ALL
SELECT CLIENTREF AS ALICI, NULL AS BAKIYE, MAX(Date_) AS SONSATISTARIHI, NULL AS SONSATISTUTARI, NULL AS SONSATICI, NULL AS SONTAHSILATTARIHI, NULL AS SONTAHSILATTUTARI, NULL
AS SONTAHSILATTURU
FROM dbo.FS_CARI AS F
WHERE (TrCode = 38)
GROUP BY CLIENTREF
UNION ALL
SELECT F.CLIENTREF AS ALICI, NULL AS BAKIYE, NULL AS SONSATISTARIHI, SUM(F.Amount) AS SONSATISTUTARI, NULL AS SONSATICI, NULL AS SONTAHSILATTARIHI, NULL AS SONTAHSILATTUTARI, NULL
AS SONTAHSILATTURU
FROM dbo.FS_CARI AS F LEFT OUTER JOIN
(SELECT CLIENTREF AS ALICI, MAX(Date_) AS TARIH
FROM dbo.FS_CARI AS F
WHERE (TrCode = 38)
GROUP BY CLIENTREF) AS SUB_3 ON F.CLIENTREF = SUB_3.ALICI
WHERE (F.TrCode = 38) AND (F.Date_ = SUB_3.TARIH)
GROUP BY F.CLIENTREF
UNION ALL
SELECT F.CLIENTREF AS ALICI, NULL AS BAKIYE, NULL AS SONSATISTARIHI, NULL AS SONSATISTUTARI, F.SalesmanRef AS SONSATICI, NULL AS SONTAHSILATTARIHI, NULL AS SONTAHSILATTUTARI, NULL
AS SONTAHSILATTURU
FROM dbo.FS_CARI AS F LEFT OUTER JOIN
(SELECT CLIENTREF AS ALICI, MAX(Date_) AS TARIH
FROM dbo.FS_CARI AS F
WHERE (TrCode = 38)
GROUP BY CLIENTREF) AS SUB_2 ON F.CLIENTREF = SUB_2.ALICI
WHERE (F.TrCode = 38) AND (F.Date_ = SUB_2.TARIH)
GROUP BY F.CLIENTREF, F.SalesmanRef
UNION ALL
SELECT CLIENTREF AS ALICI, NULL AS BAKIYE, NULL AS SONSATISTARIHI, NULL AS SONSATISTUTARI, NULL AS SONSATICI, MAX(Date_) AS SONTAHSILATTARIHI, NULL AS SONTAHSILATTUTARI, NULL
AS SONTAHSILATTURU
FROM dbo.FS_CARI AS F
WHERE (TrCode IN (1, 20, 61, 62, 70))
GROUP BY CLIENTREF
UNION ALL
SELECT F.CLIENTREF AS ALICI, NULL AS BAKIYE, NULL AS SONSATISTARIHI, NULL AS SONSATISTUTARI, NULL AS SONSATICI, NULL AS SONTAHSILATTARIHI, SUM(F.Amount) AS SONTAHSILATTUTARI, NULL
AS SONTAHSILATTURU
FROM dbo.FS_CARI AS F LEFT OUTER JOIN
(SELECT CLIENTREF AS ALICI, MAX(Date_) AS TARIH
FROM dbo.FS_CARI AS F
WHERE (TrCode IN (1, 20, 61, 62, 70))
GROUP BY CLIENTREF) AS SUB_1 ON F.CLIENTREF = SUB_1.ALICI
WHERE (F.TrCode IN (1, 20, 61, 62, 70)) AND (F.Date_ = SUB_1.TARIH)
GROUP BY F.CLIENTREF
UNION ALL
SELECT F.CLIENTREF AS ALICI, NULL AS BAKIYE, NULL AS SONSATISTARIHI, NULL AS SONSATISTUTARI, NULL AS SONSATICI, NULL AS SONTAHSILATTARIHI, NULL AS SONTAHSILATTUTARI,
F.TrCode AS SONTAHSILATTURU
FROM dbo.FS_CARI AS F LEFT OUTER JOIN
(SELECT CLIENTREF AS ALICI, MAX(Date_) AS TARIH
FROM dbo.FS_CARI AS F
WHERE (TrCode IN (1, 20, 61, 62, 70))
GROUP BY CLIENTREF) AS SUB1 ON F.CLIENTREF = SUB1.ALICI
WHERE (F.TrCode IN (1, 20, 61, 62, 70)) AND (F.Date_ = SUB1.TARIH)
GROUP BY F.CLIENTREF, F.TrCode) AS SUB LEFT OUTER JOIN
dbo.LG_017_CLCARD AS CL ON SUB.ALICI = CL.LOGICALREF LEFT OUTER JOIN
dbo.LG_SLSMAN AS SLS ON SUB.SONSATICI = SLS.LOGICALREF
WHERE (CL.CODE LIKE '120%')
GROUP BY CL.DEFINITION_
HAVING (MAX(SUB.BAKIYE) > 1000)
ORDER BY BAKIYE DESC