Controles | Unification du Référentiel Télécom

Consultation et edition

Comparer le nombre de prise par BAT

Les informations du controle se modifient directement dans le formulaire ci-dessous.

#3 Sans informations

Nouveau

Nom du controle

Nom technique

Categorie

Theme

Type

Ordre

Description

Interets

Controle actif dans le catalogue

SQL du controle

/*--- Controle Nombre de prise avec booléens ---*/
DROP TABLE IF EXISTS base_rop.ctrl_03;
CREATE TABLE base_rop.ctrl_03 AS 
WITH baserop_count AS (
    SELECT
        lrc_idnt_immeuble AS code_batiment,
        COUNT(*) AS nbr_prise
    FROM base_rop.baserop
    GROUP BY lrc_idnt_immeuble
),
easy_count AS (
    SELECT
        ad_batcode AS code_batiment,
        SUM(
            COALESCE(ad_nblhab, ad_nbprhab, 0)
          + COALESCE(ad_nblpro, ad_nbprpro, 0)
        ) AS easy
    FROM public.t_adresse
    WHERE ad_batcode IS NOT NULL
    GROUP BY ad_batcode
),
ipe_count AS (
    SELECT
        identifiantimmeuble AS code_batiment,
        SUM(
            COALESCE(NULLIF(NombreLogementsAdresseIPE,'')::integer,0)
        ) AS ipe
    FROM base_rop.ipe
    WHERE identifiantimmeuble IS NOT NULL
    GROUP BY identifiantimmeuble
)
SELECT
    ROW_NUMBER() OVER () AS id,
    'C03' AS id_ctr,
    'Comparer le nombre de prise par BAT' AS name_ctr,
    COALESCE(cb.site_easy, cb.site_ipe, cb.site_baserop) AS site_sro,
    cb.code_batiment AS donnee_ref,
    CASE
        WHEN COALESCE(e.easy,0) = COALESCE(i.ipe,0)
         AND COALESCE(e.easy,0) = COALESCE(b.nbr_prise,0)
        THEN 'OK'
        ELSE 'NOK'
    END AS statut,
    COALESCE(e.easy,0) = COALESCE(i.ipe,0) AND COALESCE(e.easy,0) = COALESCE(b.nbr_prise,0) AS easy,
    COALESCE(e.easy, 0) AS easy_value,
    COALESCE(i.ipe,0) = COALESCE(e.easy,0) AND COALESCE(i.ipe,0) = COALESCE(b.nbr_prise,0) AS ipe,
    COALESCE(i.ipe, 0) AS ipe_value,
    COALESCE(b.nbr_prise,0) = COALESCE(e.easy,0) AND COALESCE(b.nbr_prise,0) = COALESCE(i.ipe,0) AS baserop,
	COALESCE(b.nbr_prise, 0) AS baserop_value
FROM base_rop.code_batiment cb
LEFT JOIN easy_count e ON e.code_batiment = cb.code_batiment
LEFT JOIN ipe_count i ON i.code_batiment = cb.code_batiment
LEFT JOIN baserop_count b ON b.code_batiment = cb.code_batiment;

Annuler