CREATE VIEW dbo.VW_AKTIF_MUSTERI_SATIS_2024_2025
AS
WITH S2024 AS (
    SELECT
        CLIENTREF,
        SUM(AMOUNT) AS TOPLAM_2024
    FROM LG_011_07_CLFLINE
    WHERE TRCODE = 38
      AND CANCELLED = 0
    GROUP BY CLIENTREF
),
S2025 AS (
    SELECT
        CLIENTREF,
        SUM(AMOUNT) AS TOPLAM_2025
    FROM LG_011_08_CLFLINE
    WHERE TRCODE = 38
      AND CANCELLED = 0
    GROUP BY CLIENTREF
)
SELECT
    C.DEFINITION_                                AS [FIRMA ADI],
    C.CITY                                       AS [IL],
    C.DISCRATE                                   AS [ISKONTO],
    C.SPECODE2                                   AS [PERSONEL],
    COALESCE(S24.TOPLAM_2024, 0)                 AS [2024 SATIS TOPLAMLARI],
    COALESCE(S25.TOPLAM_2025, 0)                 AS [2025 SATIS TOPLAMLARI],

    COALESCE(
        CASE
            WHEN COALESCE(S24.TOPLAM_2024, 0) = 0 AND COALESCE(S25.TOPLAM_2025, 0) = 0 THEN 0
            WHEN COALESCE(S24.TOPLAM_2024, 0) = 0 AND COALESCE(S25.TOPLAM_2025, 0) <> 0 THEN NULL
            ELSE
                ( (COALESCE(S25.TOPLAM_2025, 0) - COALESCE(S24.TOPLAM_2024, 0))
                  / NULLIF(COALESCE(S24.TOPLAM_2024, 0), 0) ) * 100.0
        END
    , 0)                                         AS [YÜZDE ARTIS/AZALIS],

    CASE
        WHEN (COALESCE(S25.TOPLAM_2025, 0) - COALESCE(S24.TOPLAM_2024, 0)) > 0 THEN N'ARTIS'
        WHEN (COALESCE(S25.TOPLAM_2025, 0) - COALESCE(S24.TOPLAM_2024, 0)) < 0 THEN N'AZALIS'
        ELSE N'SABIT'
    END                                          AS [DURUM]
FROM LG_011_CLCARD C
LEFT JOIN S2024 S24 ON S24.CLIENTREF = C.LOGICALREF
LEFT JOIN S2025 S25 ON S25.CLIENTREF = C.LOGICALREF
WHERE C.ACTIVE = 0;