CREATE VIEW dbo.FIS_HESABI_CARI_BAKIYELER_SUBE
AS
SELECT   QQ.CLIENTREF, CC.CODE, CASE /* Eger CODE bir rakam ile basliyorsa (0-9), ilk 10 karakteri al*/ WHEN CC.CODE LIKE '[0-9]%' THEN REPLACE(SUBSTRING(CC.CODE, 1, 10), ' ', '') 
                         /* Eger CODE bir harf ile basliyorsa (S gibi), ilk 8 karakteri al*/ WHEN CC.CODE LIKE '[A-Z]%' THEN REPLACE(SUBSTRING(CC.CODE, 1, 8), ' ', '') 
                         /* Aksi takdirde orijinal CODE degeri döndür*/ ELSE CC.CODE END AS REV, QQ.BAKIYE AS BAKIYE
FROM         (SELECT   CLIENTREF, SUM(ALACAK) - SUM(BORC) AS BAKIYE
                           FROM         ((SELECT   CLIENTREF, TRNET AS BORC, 0 AS ALACAK
                                                       FROM         LG_011_08_CLFLINE
                                                       WHERE     [SIGN] = 0 AND CANCELLED = 0)
                                                    UNION ALL
                                                    (SELECT   CLIENTREF, 0 AS BORC, TRNET AS ALACAK
                                                     FROM         LG_011_08_CLFLINE
                                                     WHERE     [SIGN] = 1 AND CANCELLED = 0)) KK
GROUP BY CLIENTREF) AS QQ LEFT OUTER JOIN
LG_011_CLCARD AS CC ON CC.LOGICALREF = QQ.CLIENTREF
WHERE     CC.ACTIVE = 0 AND (CC.CODE LIKE '%SB%' OR
                         CC.CODE LIKE '%2S%' OR
                         CC.CODE LIKE '%3S%' OR
                         CC.CODE LIKE '%SU%' OR
                         CC.CODE LIKE '%SB%' OR
                         CC.CODE LIKE '%S.%' OR
                         CC.CODE LIKE '%-S%' OR
                         CC.CODE LIKE '%GS%') AND ABS(BAKIYE) > 1