Calculs et règles métier - PT1CE¶

-- Pour chaque borne
CASE 
    WHEN np.ID_ART IS NOT NULL AND c.ECART_PL1_PL2_PAS IS NOT NULL THEN
        np.NEW_PAS + c.ECART_PL1_PL2_PAS
    ELSE c.BORNE_PL1_PL2
END as NEW_BORNE_PL1_PL2

CASE
    WHEN np.NEW_PAS + c.ECART_PL1_PL2_PAS < np.NEW_PAS 
    THEN np.NEW_PAS
    ELSE np.NEW_PAS + c.ECART_PL1_PL2_PAS
END

CASE
    -- Si PRB_TO_USE = 1, utiliser PRB_RC
    WHEN c.PRB_TO_USE = 1 AND np.NEW_PAS + c.ECART > np.NEW_PRB_RC 
    THEN np.NEW_PRB_RC

    -- Si PRB_TO_USE = 2, utiliser PRB_COLL
    WHEN c.PRB_TO_USE = 2 AND np.NEW_PAS + c.ECART > np.NEW_PRB_COLL 
    THEN np.NEW_PRB_COLL

    -- Sinon, garder le calcul
    ELSE np.NEW_PAS + c.ECART
END

CASE 
    WHEN s.NEW_BORNE_PL6_PLX = s.NEW_PAS THEN 'SUBOPTIMAL'
    ELSE 'OPTIMAL'
END as STATUS

CASE
    -- Problème double
    WHEN s.NEW_BORNE_PL6_PLX = s.NEW_PAS 
     AND s.ECART_TYPE > 0.10 
    THEN 'PL6_ET_ECART_TYPE'

    -- Problème simple PL6
    WHEN s.NEW_BORNE_PL6_PLX = s.NEW_PAS 
    THEN 'PL6_EGAL_PAS'

    -- Écart-type seul (reste optimal)
    WHEN s.ECART_TYPE > 0.10 
    THEN 'ECART_TYPE_ELEVE'

    -- Pas de problème
    ELSE 'AUCUN'
END as PROBLEM_TYPE

-- Flag écart-type élevé
CASE 
    WHEN s.ECART_TYPE > 0.10 THEN 1 
    ELSE 0 
END as HAS_HIGH_STD

-- Flag PL6 = PAS
CASE 
    WHEN s.NEW_BORNE_PL6_PLX = s.NEW_PAS THEN 1 
    ELSE 0 
END as HAS_PL6_EQUALS_PAS

-- Vérification ordre croissant
CASE
    WHEN s.NEW_BORNE_PL1_PL2 <= s.NEW_BORNE_PL2_PL3
     AND s.NEW_BORNE_PL2_PL3 <= s.NEW_BORNE_PL3_PL4
     AND s.NEW_BORNE_PL3_PL4 <= s.NEW_BORNE_PL4_PL5
     AND s.NEW_BORNE_PL4_PL5 <= s.NEW_BORNE_PL5_PL6
     AND s.NEW_BORNE_PL5_PL6 <= s.NEW_BORNE_PL6_PLX
    THEN 'COHERENT'
    ELSE 'INCOHERENT'
END as BORNES_COHERENCE

-- Comparaison ancien/nouveau par TYPE_CLIENT
SELECT
    TYPE_CLIENT,

    -- PAS
    AVG(PAS_ACTIF) as PAS_MOY_ANCIEN,
    AVG(NEW_PAS) as PAS_MOY_NOUVEAU,
    ROUND((AVG(NEW_PAS) - AVG(PAS_ACTIF)) / AVG(PAS_ACTIF) * 100, 2) 
        as EVOL_PAS_PCT,

    -- Bornes (exemple PL1)
    AVG(BORNE_PL1_PL2) as PL1_MOY_ANCIEN,
    AVG(NEW_BORNE_PL1_PL2) as PL1_MOY_NOUVEAU,
    ROUND((AVG(NEW_BORNE_PL1_PL2) - AVG(BORNE_PL1_PL2)) 
          / AVG(BORNE_PL1_PL2) * 100, 2) as EVOL_PL1_PCT

FROM comparison_table
WHERE STATUS = 'OPTIMAL'
GROUP BY TYPE_CLIENT

SELECT
    CASE
        WHEN ECART_TYPE < 0.02 THEN '< 2%'
        WHEN ECART_TYPE < 0.05 THEN '2% - 5%'
        WHEN ECART_TYPE < 0.07 THEN '5% - 7%'
        WHEN ECART_TYPE < 0.10 THEN '7% - 10%'
        WHEN ECART_TYPE < 0.15 THEN '10% - 15%'
        WHEN ECART_TYPE < 0.20 THEN '15% - 20%'
        ELSE '> 20%'
    END as TRANCHE_ECART_TYPE,
    COUNT(*) as NB_CORRIDORS,
    SUM(CA_TOTAL) as CA_TOTAL
FROM table
GROUP BY CASE...
ORDER BY MIN(ECART_TYPE)