CREATE VIEW dbo.PLAS_RAPOR_YILLIK_SEHIR_TOPLAM
AS
SELECT   ISNULL(SLS.LOGICALREF, 0) AS LOGICALREF, CLF.YEAR_, ISNULL(SLS.DEFINITION_, 'SIRKET') AS PLS, CLC.CITY, SUM(CLF.TRNET) AS TUTAR
FROM         LG_011_08_CLFLINE AS CLF LEFT OUTER JOIN
                         LG_SLSMAN AS SLS ON CLF.SALESMANREF = SLS.LOGICALREF LEFT OUTER JOIN
                         LG_011_CLCARD AS CLC ON CLC.LOGICALREF = CLF.CLIENTREF
WHERE     CLF.TRCODE = 38 AND CLF.CANCELLED = 0
GROUP BY CLF.YEAR_, SLS.DEFINITION_, SLS.LOGICALREF, CLC.CITY
UNION ALL
SELECT   ISNULL(SLS.LOGICALREF, 0) AS LOGICALREF, CLF.YEAR_, ISNULL(SLS.DEFINITION_, 'SIRKET') AS PLS, CLC.CITY, SUM(CLF.TRNET) AS TUTAR
FROM         LG_011_07_CLFLINE AS CLF LEFT OUTER JOIN
                         LG_SLSMAN AS SLS ON CLF.SALESMANREF = SLS.LOGICALREF LEFT OUTER JOIN
                         LG_011_CLCARD AS CLC ON CLC.LOGICALREF = CLF.CLIENTREF
WHERE     CLF.TRCODE = 38 AND CLF.CANCELLED = 0
GROUP BY CLF.YEAR_, SLS.DEFINITION_, SLS.LOGICALREF, CLC.CITY
UNION ALL
SELECT   ISNULL(SLS.LOGICALREF, 0) AS LOGICALREF, CLF.YEAR_, ISNULL(SLS.DEFINITION_, 'SIRKET') AS PLS, CLC.CITY, SUM(CLF.TRNET) AS TUTAR
FROM         LG_011_06_CLFLINE AS CLF LEFT OUTER JOIN
                         LG_SLSMAN AS SLS ON CLF.SALESMANREF = SLS.LOGICALREF LEFT OUTER JOIN
                         LG_011_CLCARD AS CLC ON CLC.LOGICALREF = CLF.CLIENTREF
WHERE     CLF.TRCODE = 38 AND CLF.CANCELLED = 0
GROUP BY CLF.YEAR_, SLS.DEFINITION_, SLS.LOGICALREF, CLC.CITY