CREATE VIEW dbo.ANKARA_CARILARI
AS
SELECT        CLIENTREF, SUM(TOPLAM_SATIS_ANKARA) AS TOPLAM_SATIS_ANKARA, SUM(IADE_ANKARA) AS IADE_ANKARA, SUM(TAHSILAT_ANKARA) AS TAHSILAT_ANKARA
FROM            (SELECT        CLF.CLIENTREF, SUM(CLF.TRNET) AS TOPLAM_SATIS_ANKARA, 0 AS IADE_ANKARA, 0 AS TAHSILAT_ANKARA
                          FROM            dbo.LG_011_CLCARD AS CLC LEFT OUTER JOIN
                                                    dbo.LG_011_06_CLFLINE AS CLF ON CLC.LOGICALREF = CLF.CLIENTREF
                          WHERE        (CLC.CITY LIKE '%Ankara%') AND (CLF.TRCODE = 38)
                          GROUP BY CLF.CLIENTREF, CLC.CITY
                          UNION ALL
                          SELECT        CLF.CLIENTREF, 0 AS TOPLAM_SATIS_ANKARA, SUM(CLF.TRNET) AS IADE_ANKARA, 0 AS TAHSILAT_ANKARA
                          FROM            dbo.LG_011_CLCARD AS CLC LEFT OUTER JOIN
                                                   dbo.LG_011_06_CLFLINE AS CLF ON CLC.LOGICALREF = CLF.CLIENTREF
                          WHERE        (CLC.CITY LIKE '%Ankara%') AND (CLF.TRCODE = 33)
                          GROUP BY CLF.CLIENTREF, CLC.CITY
                          UNION ALL
                          SELECT        CLF.CLIENTREF, 0 AS TOPLAM_SATIS_ANKARA, 0 AS IADE_ANKARA, SUM(CLF.TRNET) AS ASTAHSILAT_ANKARA
                          FROM            dbo.LG_011_CLCARD AS CLC LEFT OUTER JOIN
                                                   dbo.LG_011_06_CLFLINE AS CLF ON CLC.LOGICALREF = CLF.CLIENTREF
                          WHERE        (CLC.CITY LIKE '%Ankara%') AND (CLF.TRCODE IN (20, 1, 61, 62, 70))
                          GROUP BY CLF.CLIENTREF, CLC.CITY) AS QQ
GROUP BY CLIENTREF