CREATE VIEW dbo.CARI_DURUM_TSI
AS
SELECT
QQ.SPECODE2,
QQ.CITY,
SUM(FRMASAY) AS FRMASAY,
QQ.TUR,
QQ.YIL
FROM            ((SELECT        CASE WHEN SPECODE2='ÜMIT' AND CITY='Istanbul' THEN 'MUHAMMED' ELSE SPECODE2 END AS SPECODE2, CITY, COUNT(DISTINCT CODE) AS FRMASAY, 1 AS TUR, YIL
                        FROM            [UNIVERSALDB].[dbo].[PLASIYER_TSI_LISTESI]
                            WHERE        TUR IN (1, 2) AND CODE NOT LIKE 'Z%' AND CODE NOT LIKE 'K%' AND CODE NOT LIKE 'AZ%'
                            GROUP BY SPECODE2, CITY, YIL)
                         UNION ALL
                         (SELECT        CASE WHEN SPECODE2='ÜMIT' AND CITY='Istanbul' THEN 'MUHAMMED' ELSE SPECODE2 END AS SPECODE2, CITY, COUNT(*) AS FIRMASAY, 2 AS TUR, 2024 AS YIL
                          FROM            LG_011_CLCARD
                          WHERE        ACTIVE = 0 AND CODE NOT LIKE 'Z%' AND CODE NOT LIKE 'K%' AND CODE NOT LIKE 'AZ%' AND CITY <> ''
                          GROUP BY SPECODE2, CITY)) QQ
						  WHERE QQ.CITY<>'' AND QQ.SPECODE2<>''
						   GROUP BY QQ.SPECODE2,
QQ.CITY,
QQ.TUR,
QQ.YIL