CREATE VIEW dbo.FIS_HESABI_CARI_BAKIYELER
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 '% %' OR
                         CC.CODE LIKE '%F%' OR
                         CC.CODE LIKE '%M%' OR
                         CC.CODE LIKE '%S%') AND ABS(QQ.BAKIYE) > 1