CREATE VIEW dbo.PLAS_LIST_TSI
AS
WITH CTE_MaxSpecode AS
(
    SELECT
        PL.CITY,
        PL.SPECODE2,
        -- Sehir ve SPECODE2 bazinda satir sayisi
        COUNT(*) AS SpecodeCount,
        -- Her CITY içinde en çok tekrar eden SPECODE2'ye 1 numara veriyoruz
        ROW_NUMBER() OVER (
            PARTITION BY PL.CITY
            ORDER BY COUNT(*) DESC
        ) AS rn
    FROM dbo.LG_011_CLCARD AS PL
    WHERE COALESCE(PL.SPECODE2, '') <> ''   -- Bos (NULL veya '')  SPECODE2’leri dahil etme
    GROUP BY PL.CITY, PL.SPECODE2
)
SELECT 
    PL.SPECODE2,
    PL.CITY,
    PL.MANG,
    PL.COLOR_,
    PL.LAT,
    PL.LNG,
    PL.MANG_COLOR,
    ISNULL(TSI.TOPLAM, 0)  AS TAH2023,
    ISNULL(TSI5.TOPLAM, 0) AS TAH2024,
    ISNULL(TSI3.TOPLAM, 0) AS SAT2023,
    ISNULL(TSI6.TOPLAM, 0) AS SAT2024,
    ISNULL(TSI4.TOPLAM, 0) AS IAD2023,
    ISNULL(TSI2.TOPLAM, 0) AS IAD2024,
    ISNULL(CDT.FRMASAY, 0)  AS ISLEM2023,
    ISNULL(CDT2.FRMASAY, 0) AS ISLEM2024,
    ISNULL(CDT3.FRMASAY, 0) AS AKTIF
FROM CTE_MaxSpecode AS C
INNER JOIN dbo.PLAS_LIST AS PL
    ON  PL.CITY     = C.CITY
    AND PL.SPECODE2 = C.SPECODE2
    AND C.rn = 1  -- Sadece en çok tekrar eden SPECODE2
    -- Bos SPECODE2'leri de CTE içinde zaten filtrelemistik, 
    -- ancak yine de ek bir önlem isterseniz AND PL.SPECODE2 <> '' kullanabilirsiniz.
LEFT JOIN dbo.CARI_DURUM_TSI AS CDT
    ON  CDT.CITY     = PL.CITY
    AND CDT.SPECODE2 = PL.SPECODE2
    AND CDT.TUR      = 1
    AND CDT.YIL      = 2023
LEFT JOIN dbo.CARI_DURUM_TSI AS CDT2
    ON  CDT2.CITY     = PL.CITY
    AND CDT2.SPECODE2 = PL.SPECODE2
    AND CDT2.TUR      = 1
    AND CDT2.YIL      = 2024
LEFT JOIN dbo.CARI_DURUM_TSI AS CDT3
    ON  CDT3.CITY     = PL.CITY
    AND CDT3.SPECODE2 = PL.SPECODE2
    AND CDT3.TUR      = 2
LEFT JOIN dbo.PLASIYER_SEHIR_YILLARA_GORE_TSI_TOPLAMLARI AS TSI
    ON  TSI.PDEF2 = PL.SPECODE2
    AND TSI.CITY  = PL.CITY
    AND TSI.YIL   = 2023
    AND TSI.TUR   = 1
LEFT JOIN dbo.PLASIYER_SEHIR_YILLARA_GORE_TSI_TOPLAMLARI AS TSI3
    ON  TSI3.PDEF2 = PL.SPECODE2
    AND TSI3.CITY  = PL.CITY
    AND TSI3.YIL   = 2023
    AND TSI3.TUR   = 2
LEFT JOIN dbo.PLASIYER_SEHIR_YILLARA_GORE_TSI_TOPLAMLARI AS TSI4
    ON  TSI4.PDEF2 = PL.SPECODE2
    AND TSI4.CITY  = PL.CITY
    AND TSI4.YIL   = 2023
    AND TSI4.TUR   = 3
LEFT JOIN dbo.PLASIYER_SEHIR_YILLARA_GORE_TSI_TOPLAMLARI AS TSI5
    ON  TSI5.PDEF2 = PL.SPECODE2
    AND TSI5.CITY  = PL.CITY
    AND TSI5.YIL   = 2024
    AND TSI5.TUR   = 1
LEFT JOIN dbo.PLASIYER_SEHIR_YILLARA_GORE_TSI_TOPLAMLARI AS TSI6
    ON  TSI6.PDEF2 = PL.SPECODE2
    AND TSI6.CITY  = PL.CITY
    AND TSI6.YIL   = 2024
    AND TSI6.TUR   = 2
LEFT JOIN dbo.PLASIYER_SEHIR_YILLARA_GORE_TSI_TOPLAMLARI AS TSI2
    ON  TSI2.PDEF2 = PL.SPECODE2
    AND TSI2.CITY  = PL.CITY
    AND TSI2.YIL   = 2024
    AND TSI2.TUR   = 3;