Aller au contenu principal

Requêtes pour publipostage

Equipe médicale MesVaccins.net
  • ()
  • Mise à jour

Ces requêtes sont directement utilisables dans le module « Import / Export », onglet « Export avancé ». Elles permettent d’établir des listes de patients par catégorie d’âge, à convoquer pour la mise à jour des vaccinations.

Pour chaque requête, nous donnons la marche à suivre pour la récupération des informations indispensables, étape par étape :

  • Sélection des patients : oui signifie « Récupérer les patients ». Il est possible d'ajouter un filtre supplémentaire à cette étape (sexe, tag ou smart tag, date de naissance minimale et maximale). La requête sera ensuite exécutée sur cette sélection de dossiers ;
  • Récupération des carnets : oui signifie que vous devez cliquer sur « Récupérer les carnets », non que vous devez choisir « Passer cette étape » ;
  • Récupération des diagnostics : oui signifie que vous devez cliquer sur « Récupérer les diagnostics », non que vous devez choisir « Passer cette étape » ;
  • Édition de la requête : collez ici la requête que vous souhaitez exécuter telle qu'elle est affichée dans le rectangle de présentation (texte en bleu) après en avoir si besoin modifié les paramètres *;
  • Enfin, cliquez sur le bouton « Exécuter la requête » : le résultat s'affiche sous forme de tableau exportable au format CSV ou XLS.

* les paramètres de la requêtes sont placés en début de requête dans la section :

WITH params AS (
    SELECT
    ....
)

Il peut s'agir de date minimale, d'intervalle entre doses... permettant d'adapter la requête à vos besoins.

Pour chaque requête, il est possible d'exclure des groupes de patients en fonction de tags. Par exemple :
[''] AS tags_exclus  : aucune exclusion (par défaut)
['exclusion', 'A supprimer'] AS tags_exclus  : exclut les patients ayant l’un des deux tags.

Le groupe de patients sélectionné à l’étape 1 ne doit pas excéder 10 000 personnes. Au-delà de ce seuil, il est impossible de récupérer les éléments du carnet de vaccination ou du diagnostic vaccinal.
Si la sélection dépasse 10 000 patients, il est nécessaire de la segmenter (par exemple par sexe, afin de diviser l’effectif en deux, ou par tranches d’âge plus restreintes).

Liste des requêtes disponibles :

Liste des nourrissons de 7 mois à convoquer pour mise à jour des vaccinations contre : DTCaP-Hib, méningocoques ACWY et B, pneumocoques, hépatite B et rotavirus

  • Sélection des patients : oui
  • Récupération des carnets : oui
  • Récupération des diagnostics : non

Algorithme : 

Est convoqué pour une dose  de : 

  • DTPCa-Hib si n'a pas reçu 2 doses de vaccin INFANRIX HEXA, HEXYON ou VAXELYS espacées d’au moins 45 jours ;
  • Pneumocoque si n'a pas reçu 2 doses de vaccin PREVENAR 13 ou VAXNEUVANCE espacées d’au moins 45 jours ;
  • Hépatite B si n'a pas reçu 2 doses de vaccin INFANRIX HEXA, HEXYON ou VAXELYS espacées d’au moins 45 jours ;
  • Méningocoques ACWY si n'a pas reçu 1 dose de NIMENRIX ;
  • Méningocoques B si n'a pas reçu 2 doses de vaccin BEXSERO espacées d’au moins 45 jours ;
  • Rotavirus si n'a pas reçu 3 doses de vaccin ROTATEQ.

NB : l'intervalle de validité entre la première et la 2e dose, fixé par défaut à 45 jours, peut être modifié dans les paramètres de la requêtes, pour les vaccins DTPCa-Hib, hépatite B, pneumocoque et méningocoque B. Si l'intervalle entre D1 et D2 est de 60 jours dans le RCP, à posteriori, il est possible d'appliquer une tolérance si le schéma n'a pas été strictement appliqué, sans ajouter de dose supplémentaire. La tolérance est ici de 15 jours (60 - 15 = 45 jours d'intervalle).

Les colonnes O à X permettent de contrôler le nombre de doses administrées et l'écart entre D1 et D2 si nécessaire. 

La dernière colonne indique les maladies pour lesquelles le nourrisson doit être convoqué.

L'export Excel des résultats de cette requête est prêt pour une exploitation en publipostage (par voie postale, e-mail ou SMS, tous les champs nécessaires étant présents).

WITH params AS (
  SELECT
  /* une tolérance de 15 jours a été appliquée entre D1 et D2 par rapport au RCP */
    45 AS intervalle_min_dtp_jours,     -- DTPCa-Hib : intervalle minimal
    45 AS intervalle_min_hepb_jours,    -- Hépatite B : intervalle minimal
    45 AS intervalle_min_pneumo_jours,  -- Pneumo : intervalle minimal
    45 AS intervalle_min_menb_jours,    -- Méningo B : intervalle minimal
    [''] AS tags_exclus                 -- Liste des tags à exclure
),

/* =========================
   Méningocoque ACWY
   ========================= */
acwy_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_acwy
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '54754014-a3fc-42bb-b007-1a2024eca164' -- Nimenrix
  )
  GROUP BY v.patient_id
),

/* =========================
   MENINGO B
   ========================= */
menb_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_menb,

    -- D1/D2 = 1e et 2e dose (ordre chronologique)
    list_extract(list(v.performed_on ORDER BY v.performed_on ASC), 1) AS d1_menb,
    list_extract(list(v.performed_on ORDER BY v.performed_on ASC), 2) AS d2_menb
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '920c7a42-e375-449c-8f5c-4212c329379f' -- BEXSERO
  )
  GROUP BY v.patient_id
),

/* =========================
   DTPC-HIB
   ========================= */
dtp_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_dtp,

    -- D1/D2 = 1e et 2e dose (ordre chronologique)
    list_extract(list(v.performed_on ORDER BY v.performed_on ASC), 1) AS d1_dtp,
    list_extract(list(v.performed_on ORDER BY v.performed_on ASC), 2) AS d2_dtp
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA
    '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON
    'b9562145-be50-420f-a809-dd0e2762d142'  -- VAXELIS
  )
  GROUP BY v.patient_id
),

/* =========================
   Hépatite B
   ========================= */
hepb_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_hepb,

    -- D1/D2 = 1e et 2e dose (ordre chronologique)
    list_extract(list(v.performed_on ORDER BY v.performed_on ASC), 1) AS d1_hepb,
    list_extract(list(v.performed_on ORDER BY v.performed_on ASC), 2) AS d2_hepb
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA
    '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON
    'b9562145-be50-420f-a809-dd0e2762d142'  -- VAXELIS
  )
  GROUP BY v.patient_id
),

/* =========================
   PNEUMO
   ========================= */
pneumo_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_pneumo,

    -- D1/D2 = 1e et 2e dose (ordre chronologique)
    list_extract(list(v.performed_on ORDER BY v.performed_on ASC), 1) AS d1_pneumo,
    list_extract(list(v.performed_on ORDER BY v.performed_on ASC), 2) AS d2_pneumo
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '5312b079-3245-49fe-9193-4658c4c5d1d0', -- PREVENAR 13
    '5e6dd99c-ef7c-4bbd-8611-a3be793d8456'  -- VAXNEUVANCE
  )
  GROUP BY v.patient_id
),

/* =========================
   ROTAVIRUS
   ========================= */
rota_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_rota
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '06b40526-a35e-45b2-81ca-3cce355033b2' -- ROTATEQ
  )
  GROUP BY v.patient_id
),

patients_enrichis AS (
  SELECT
    patients.id,
    patients.last_name   AS Nom,
    patients.first_names AS Prénom,
    patients.gender      AS Sexe,
    patients.birth_date  AS "Date de naissance",
    patients.tags,
    patients.created_at  AS "Création du dossier",
    patients.address1 AS "Adresse",
    patients.address2 AS "Complément d'adresse",
    patients.city_zip_code AS "Code postal",
    patients.city AS "Commune",
    patients.email AS "Email",
    patients.email_2 AS "Email secondaire",
    patients.phone_number AS "Téléphone",
    patients.phone_number_2 AS "Téléphone secondaire",

    -- Âge en mois (pour filtrer les enfants de 7 mois)
    date_diff('month', patients.birth_date, current_date) AS age_mois,

    coalesce(a.nbre_doses_acwy, 0) AS nbre_doses_acwy,

    coalesce(m.nbre_doses_menb, 0) AS nbre_doses_menb,
    m.d1_menb,
    m.d2_menb,
    -- Ecart d1d2 MENINGO (1e–2e dose, en jours) -> 0 si même jour
    CASE
      WHEN coalesce(m.nbre_doses_menb, 0) >= 2
       AND m.d1_menb IS NOT NULL
       AND m.d2_menb IS NOT NULL
      THEN date_diff('day', m.d1_menb, m.d2_menb)
      ELSE NULL
    END AS ecart_d1d2_menb_jours,

    coalesce(d.nbre_doses_dtp, 0) AS nbre_doses_dtp,
    d.d1_dtp,
    d.d2_dtp,
    -- Ecart d1d2 DTPCa-HIB (1e–2e dose, en jours)
    CASE
      WHEN coalesce(d.nbre_doses_dtp, 0) >= 2
       AND d.d1_dtp IS NOT NULL
       AND d.d2_dtp IS NOT NULL
      THEN date_diff('day', d.d1_dtp, d.d2_dtp)
      ELSE NULL
    END AS ecart_d1d2_dtp_jours,

    coalesce(h.nbre_doses_hepb, 0) AS nbre_doses_hepb,
    h.d1_hepb,
    h.d2_hepb,
    -- Ecart d1d2 Hépatite B (1e–2e dose, en jours)
    CASE
      WHEN coalesce(h.nbre_doses_hepb, 0) >= 2
       AND h.d1_hepb IS NOT NULL
       AND h.d2_hepb IS NOT NULL
      THEN date_diff('day', h.d1_hepb, h.d2_hepb)
      ELSE NULL
    END AS ecart_d1d2_hepb_jours,

    coalesce(p.nbre_doses_pneumo, 0) AS nbre_doses_pneumo,
    p.d1_pneumo,
    p.d2_pneumo,
    -- Ecart d1d2 Pneumocoque (1e–2e dose, en jours)
    CASE
      WHEN coalesce(p.nbre_doses_pneumo, 0) >= 2
       AND p.d1_pneumo IS NOT NULL
       AND p.d2_pneumo IS NOT NULL
      THEN date_diff('day', p.d1_pneumo, p.d2_pneumo)
      ELSE NULL
    END AS ecart_d1d2_pneumo_jours,

    coalesce(r.nbre_doses_rota, 0) AS nbre_doses_rota,

    params.intervalle_min_hepb_jours,
    params.intervalle_min_dtp_jours,
    params.intervalle_min_menb_jours,
    params.intervalle_min_pneumo_jours,
    params.tags_exclus,

    /* ===== Flags "à faire" (booléens) ===== */
    (coalesce(a.nbre_doses_acwy, 0) = 0) AS acwy_a_faire,
    (coalesce(r.nbre_doses_rota, 0) < 3) AS rota_a_faire,

    NOT (
      coalesce(m.nbre_doses_menb, 0) >= 2
      AND m.d1_menb IS NOT NULL
      AND m.d2_menb IS NOT NULL
      AND date_diff('day', m.d1_menb, m.d2_menb) >= params.intervalle_min_menb_jours
    ) AS menb_a_faire,

    NOT (
      coalesce(d.nbre_doses_dtp, 0) >= 2
      AND d.d1_dtp IS NOT NULL
      AND d.d2_dtp IS NOT NULL
      AND date_diff('day', d.d1_dtp, d.d2_dtp) >= params.intervalle_min_dtp_jours
    ) AS dtcp_a_faire,

    NOT (
      coalesce(h.nbre_doses_hepb, 0) >= 2
      AND h.d1_hepb IS NOT NULL
      AND h.d2_hepb IS NOT NULL
      AND date_diff('day', h.d1_hepb, h.d2_hepb) >= params.intervalle_min_hepb_jours
    ) AS hepb_a_faire,

    NOT (
      coalesce(p.nbre_doses_pneumo, 0) >= 2
      AND p.d1_pneumo IS NOT NULL
      AND p.d2_pneumo IS NOT NULL
      AND date_diff('day', p.d1_pneumo, p.d2_pneumo) >= params.intervalle_min_pneumo_jours
    ) AS pneumo_a_faire

  FROM patients
  CROSS JOIN params
  LEFT JOIN acwy_par_patient a   ON a.patient_id = patients.id
  LEFT JOIN menb_par_patient m   ON m.patient_id = patients.id
  LEFT JOIN dtp_par_patient d    ON d.patient_id = patients.id
  LEFT JOIN hepb_par_patient h   ON h.patient_id = patients.id
  LEFT JOIN pneumo_par_patient p ON p.patient_id = patients.id
  LEFT JOIN rota_par_patient r   ON r.patient_id = patients.id
)

SELECT
  Nom,
  Prénom,
  Sexe,
  "Date de naissance",
  "Adresse",
  "Complément d'adresse",
  "Code postal",
  "Commune",
  "Email",
  "Email secondaire",
  "Téléphone",
  "Téléphone secondaire",
  
  /* Âge (avec gestion singulier + semaines entre parenthèses) */
  CASE
    -- Moins d’un an → uniquement en mois
    WHEN age_mois < 12
    THEN
      CAST(age_mois AS INTEGER) || ' mois'

    -- 1 an
    WHEN CAST(age_mois / 12 AS INTEGER) = 1
    THEN
      '1 an ' || CAST(age_mois % 12 AS INTEGER) || ' mois'

    -- 2 ans et +
    ELSE
      CAST(age_mois / 12 AS INTEGER)
      || ' ans '
      || CAST(age_mois % 12 AS INTEGER)
      || ' mois'
  END
  || ' ('
  || CAST(date_diff('day', "Date de naissance", current_date) / 7 AS INTEGER)
  || ' semaines)'
  AS "Âge ce jour",
  
  "Création du dossier",

  /* ===== ACWY ===== */
  nbre_doses_acwy AS "Doses ACWY",

  /* ===== MENINGO B ===== */
  nbre_doses_menb AS "Doses Men B",
  ecart_d1d2_menb_jours AS "Ecart d1d2 Men B (jours)",

  /* ===== DTPCa-HIB ===== */
  nbre_doses_dtp AS "Doses DTPCa-HIB",
  ecart_d1d2_dtp_jours AS "Ecart d1d2 DTPCa-HIB (jours)",

  /* ===== Hépatite B ===== */
  nbre_doses_hepb AS "Doses HVB",
  ecart_d1d2_hepb_jours AS "Ecart d1d2 HVB (jours)",

  /* ===== PNEUMO ===== */
  nbre_doses_pneumo AS "Doses Pneumo",
  ecart_d1d2_pneumo_jours AS "Ecart d1d2 Pneumo (jours)",

  /* ===== ROTAVIRUS ===== */
  nbre_doses_rota AS "Doses Rotavirus",

  /* ===== Liste des maladies à faire ===== */
  array_to_string(
    list_filter(
      list_value(
        CASE WHEN acwy_a_faire   THEN 'Méningocoque ACWY' END,
        CASE WHEN menb_a_faire   THEN 'Méningocoque B' END,
        CASE WHEN dtcp_a_faire   THEN 'DTCP-Hib' END,
        CASE WHEN hepb_a_faire   THEN 'Hépatite B' END,
        CASE WHEN pneumo_a_faire THEN 'Pneumocoque' END,
        CASE WHEN rota_a_faire   THEN 'Rotavirus' END
      ),
      x -> x IS NOT NULL
    ),
    ', '
  ) AS "Maladies à faire"

FROM patients_enrichis
WHERE age_mois = 7 -- Ne garder que les enfants ayant 7 mois
 AND NOT EXISTS (
  SELECT 1
  FROM unnest(patients_enrichis.tags_exclus) AS t(tag)
  WHERE list_contains(
    list_transform(coalesce(patients_enrichis.tags, []), x -> lower(x)),
    lower(tag)
    )
  )
ORDER BY Nom, Prénom;

Liste des nourrissons de 24 mois à convoquer pour mise à jour des vaccinations contre : DTCaP-Hib, méningocoques ACWY et B, pneumocoques, hépatite B, ROR et grippe saisonnière

  • Sélection des patients : oui en sélectionnant 24 mois comme âge minimum et 25 mois comme âge maximum
  • Récupération des carnets : oui
  • Récupération des diagnostics : oui

Algorithme : 

Est convoqué pour une dose  de : 

  • Les enfants non à jour dans l'audit vaccinal pour les maladies citées.

La dernière colonne indique les maladies pour lesquelles le nourrisson doit être convoqué.

L'export Excel des résultats de cette requête est prêt pour une exploitation en publipostage (par voie postale, e-mail ou SMS, tous les champs nécessaires étant présents).

WITH params AS (
  SELECT
    [''] AS tags_exclus                 -- Liste des tags à exclure
)

SELECT
  p.last_name      AS "Nom",
  p.first_names    AS "Prénom",
  p.gender         AS "Sexe",
  p.birth_date     AS "Date de naissance",
  
  (
    EXTRACT(YEAR FROM AGE(CURRENT_DATE, p.birth_date)) * 12
    + EXTRACT(MONTH FROM AGE(CURRENT_DATE, p.birth_date))
  )::int
  || ' m '
  || EXTRACT(DAY FROM AGE(CURRENT_DATE, p.birth_date))::int
  || ' j'
  AS age,
  
  p.created_at     AS "Création du dossier",
  p.address1       AS "Adresse",
  p.address2       AS "Complément d'adresse",
  p.city_zip_code  AS "Code postal",
  p.city           AS "Commune",
  p.email          AS "Email",
  p.email_2        AS "Email secondaire",
  p.phone_number   AS "Téléphone",
  p.phone_number_2 AS "Téléphone secondaire",

  -- Diphtérie
  MAX(CASE WHEN dd.disease_id = '47cac099-7918-4329-9079-bdeb057957f0'
           THEN CASE
                  WHEN dd.conclusion = 'late'       THEN 'En retard'
                  WHEN dd.conclusion = 'todo'       THEN 'AF'
                  WHEN dd.conclusion = 'up_to_date' THEN 'A jour'
				  WHEN dd.conclusion = 'unmanaged' THEN 'Cas particulier'
                  ELSE dd.conclusion
                END
      END) AS diphterie_conclusion,
  MAX(CASE WHEN dd.disease_id = '47cac099-7918-4329-9079-bdeb057957f0' THEN dd.targeted_date END) AS diphterie_targeted_date,

  -- Tétanos
  MAX(CASE WHEN dd.disease_id = 'ae4a5fbe-9c4a-4836-abd0-da0247cadf42'
           THEN CASE
                  WHEN dd.conclusion = 'late'       THEN 'En retard'
                  WHEN dd.conclusion = 'todo'       THEN 'AF'
                  WHEN dd.conclusion = 'up_to_date' THEN 'A jour'
				  WHEN dd.conclusion = 'unmanaged' THEN 'Cas particulier'
                  ELSE dd.conclusion
                END
      END) AS tetanos_conclusion,
  MAX(CASE WHEN dd.disease_id = 'ae4a5fbe-9c4a-4836-abd0-da0247cadf42' THEN dd.targeted_date END) AS tetanos_targeted_date,

  -- Polio
  MAX(CASE WHEN dd.disease_id = '943d1be1-e2c9-47db-b938-03eab0fac7b0'
           THEN CASE
                  WHEN dd.conclusion = 'late'       THEN 'En retard'
                  WHEN dd.conclusion = 'todo'       THEN 'AF'
                  WHEN dd.conclusion = 'up_to_date' THEN 'A jour'
				  WHEN dd.conclusion = 'unmanaged' THEN 'Cas particulier'
                  ELSE dd.conclusion
                END
      END) AS polio_conclusion,
  MAX(CASE WHEN dd.disease_id = '943d1be1-e2c9-47db-b938-03eab0fac7b0' THEN dd.targeted_date END) AS polio_targeted_date,

  -- Coqueluche
  MAX(CASE WHEN dd.disease_id = '7cd78fe4-3e8e-4d2b-b9fb-7c0f6c57c8dc'
           THEN CASE
                  WHEN dd.conclusion = 'late'       THEN 'En retard'
                  WHEN dd.conclusion = 'todo'       THEN 'AF'
                  WHEN dd.conclusion = 'up_to_date' THEN 'A jour'
				  WHEN dd.conclusion = 'unmanaged' THEN 'Cas particulier'
                  ELSE dd.conclusion
                END
      END) AS coqueluche_conclusion,
  MAX(CASE WHEN dd.disease_id = '7cd78fe4-3e8e-4d2b-b9fb-7c0f6c57c8dc' THEN dd.targeted_date END) AS coqueluche_targeted_date,

  -- Hib
  MAX(CASE WHEN dd.disease_id = 'a3771c39-a733-459d-a3a3-31b3f2f6240a'
           THEN CASE
                  WHEN dd.conclusion = 'late'       THEN 'En retard'
                  WHEN dd.conclusion = 'todo'       THEN 'AF'
                  WHEN dd.conclusion = 'up_to_date' THEN 'A jour'
				  WHEN dd.conclusion = 'unmanaged' THEN 'Cas particulier'
                  ELSE dd.conclusion
                END
      END) AS hib_conclusion,
  MAX(CASE WHEN dd.disease_id = 'a3771c39-a733-459d-a3a3-31b3f2f6240a' THEN dd.targeted_date END) AS hib_targeted_date,

  -- Hépatite B
  MAX(CASE WHEN dd.disease_id = '3fe82df3-f5ae-4054-9f7c-0f2701aa6bfe'
           THEN CASE
                  WHEN dd.conclusion = 'late'       THEN 'En retard'
                  WHEN dd.conclusion = 'todo'       THEN 'AF'
                  WHEN dd.conclusion = 'up_to_date' THEN 'A jour'
				  WHEN dd.conclusion = 'unmanaged' THEN 'Cas particulier'
                  ELSE dd.conclusion
                END
      END) AS hepatite_b_conclusion,
  MAX(CASE WHEN dd.disease_id = '3fe82df3-f5ae-4054-9f7c-0f2701aa6bfe' THEN dd.targeted_date END) AS hepatite_b_targeted_date,

  -- Pneumocoque
  MAX(CASE WHEN dd.disease_id = '084a8856-671b-4335-a96e-a311f3fc4495'
           THEN CASE
                  WHEN dd.conclusion = 'late'       THEN 'En retard'
                  WHEN dd.conclusion = 'todo'       THEN 'AF'
                  WHEN dd.conclusion = 'up_to_date' THEN 'A jour'
				  WHEN dd.conclusion = 'unmanaged' THEN 'Cas particulier'
                  ELSE dd.conclusion
                END
      END) AS pneumocoque_conclusion,
  MAX(CASE WHEN dd.disease_id = '084a8856-671b-4335-a96e-a311f3fc4495' THEN dd.targeted_date END) AS pneumocoque_targeted_date,

  -- Méningocoque B
  MAX(CASE WHEN dd.disease_id = '49722121-d39b-45b3-af76-8689e7927320'
           THEN CASE
                  WHEN dd.conclusion = 'late'       THEN 'En retard'
                  WHEN dd.conclusion = 'todo'       THEN 'AF'
                  WHEN dd.conclusion = 'up_to_date' THEN 'A jour'
				  WHEN dd.conclusion = 'unmanaged' THEN 'Cas particulier'
                  ELSE dd.conclusion
                END
      END) AS meningocoque_b_conclusion,
  MAX(CASE WHEN dd.disease_id = '49722121-d39b-45b3-af76-8689e7927320' THEN dd.targeted_date END) AS meningocoque_b_targeted_date,

  -- Méningocoque W
  MAX(CASE WHEN dd.disease_id = '2476da72-6573-46c0-918e-d363f8337623'
           THEN CASE
                  WHEN dd.conclusion = 'late'       THEN 'En retard'
                  WHEN dd.conclusion = 'todo'       THEN 'AF'
                  WHEN dd.conclusion = 'up_to_date' THEN 'A jour'
				  WHEN dd.conclusion = 'unmanaged' THEN 'Cas particulier'
                  ELSE dd.conclusion
                END
      END) AS meningocoque_acwy_conclusion,
  MAX(CASE WHEN dd.disease_id = '2476da72-6573-46c0-918e-d363f8337623' THEN dd.targeted_date END) AS meningocoque_acwy_targeted_date,

  -- Rougeole
  MAX(CASE WHEN dd.disease_id = '5de53894-adb3-4639-9ce1-0c12012d9ecf'
           THEN CASE
                  WHEN dd.conclusion = 'late'       THEN 'En retard'
                  WHEN dd.conclusion = 'todo'       THEN 'AF'
                  WHEN dd.conclusion = 'up_to_date' THEN 'A jour'
				  WHEN dd.conclusion = 'unmanaged' THEN 'Cas particulier'
                  ELSE dd.conclusion
                END
      END) AS rougeole_conclusion,
  MAX(CASE WHEN dd.disease_id = '5de53894-adb3-4639-9ce1-0c12012d9ecf' THEN dd.targeted_date END) AS rougeole_targeted_date,

  -- Oreillons
  MAX(CASE WHEN dd.disease_id = '648d9876-393c-4a35-9fe4-bb67af2f5573'
           THEN CASE
                  WHEN dd.conclusion = 'late'       THEN 'En retard'
                  WHEN dd.conclusion = 'todo'       THEN 'AF'
                  WHEN dd.conclusion = 'up_to_date' THEN 'A jour'
				  WHEN dd.conclusion = 'unmanaged' THEN 'Cas particulier'
                  ELSE dd.conclusion
                END
      END) AS oreillons_conclusion,
  MAX(CASE WHEN dd.disease_id = '648d9876-393c-4a35-9fe4-bb67af2f5573' THEN dd.targeted_date END) AS oreillons_targeted_date,

  -- Rubéole
  MAX(CASE WHEN dd.disease_id = '5adf2b1f-8df9-4b71-8c43-e097da7d85a8'
           THEN CASE
                  WHEN dd.conclusion = 'late'       THEN 'En retard'
                  WHEN dd.conclusion = 'todo'       THEN 'AF'
                  WHEN dd.conclusion = 'up_to_date' THEN 'A jour'
				  WHEN dd.conclusion = 'unmanaged' THEN 'Cas particulier'
                  ELSE dd.conclusion
                END
      END) AS rubeole_conclusion,
  MAX(CASE WHEN dd.disease_id = '5adf2b1f-8df9-4b71-8c43-e097da7d85a8' THEN dd.targeted_date END) AS rubeole_targeted_date,

  -- Grippe saisonnière
  MAX(CASE WHEN dd.disease_id = '31f90c1a-da1c-4b2c-b894-ff797c2aff5c'
           THEN CASE
                  WHEN dd.conclusion = 'late'       THEN 'En retard'
                  WHEN dd.conclusion = 'todo'       THEN 'AF'
                  WHEN dd.conclusion = 'up_to_date' THEN 'A jour'
				  WHEN dd.conclusion = 'unmanaged' THEN 'Cas particulier'
                  ELSE dd.conclusion
                END
      END) AS grippe_conclusion,
  MAX(CASE WHEN dd.disease_id = '31f90c1a-da1c-4b2c-b894-ff797c2aff5c' THEN dd.targeted_date END) AS grippe_targeted_date,

  -- Colonne globale A FAIRE (ordre imposé)
  STRING_AGG(
    CASE
      WHEN dd.disease_id = '47cac099-7918-4329-9079-bdeb057957f0'
       AND dd.conclusion IN ('late','todo')
       AND dd.targeted_date < CURRENT_DATE
      THEN 'Diphtérie'
      WHEN dd.disease_id = 'ae4a5fbe-9c4a-4836-abd0-da0247cadf42'
       AND dd.conclusion IN ('late','todo')
       AND dd.targeted_date < CURRENT_DATE
      THEN 'Tétanos'
      WHEN dd.disease_id = '943d1be1-e2c9-47db-b938-03eab0fac7b0'
       AND dd.conclusion IN ('late','todo')
       AND dd.targeted_date < CURRENT_DATE
      THEN 'Polio'
      WHEN dd.disease_id = '7cd78fe4-3e8e-4d2b-b9fb-7c0f6c57c8dc'
       AND dd.conclusion IN ('late','todo')
       AND dd.targeted_date < CURRENT_DATE
      THEN 'Coqueluche'
      WHEN dd.disease_id = 'a3771c39-a733-459d-a3a3-31b3f2f6240a'
       AND dd.conclusion IN ('late','todo')
       AND dd.targeted_date < CURRENT_DATE
      THEN 'Hib'
      WHEN dd.disease_id = '3fe82df3-f5ae-4054-9f7c-0f2701aa6bfe'
       AND dd.conclusion IN ('late','todo')
       AND dd.targeted_date < CURRENT_DATE
      THEN 'Hépatite B'
      WHEN dd.disease_id = '084a8856-671b-4335-a96e-a311f3fc4495'
       AND dd.conclusion IN ('late','todo')
       AND dd.targeted_date < CURRENT_DATE
      THEN 'Pneumocoque'
      WHEN dd.disease_id = '49722121-d39b-45b3-af76-8689e7927320'
       AND dd.conclusion IN ('late','todo')
       AND dd.targeted_date < CURRENT_DATE
      THEN 'Méningocoque B'
      WHEN dd.disease_id = '2476da72-6573-46c0-918e-d363f8337623'
       AND dd.conclusion IN ('late','todo')
       AND dd.targeted_date < CURRENT_DATE
      THEN 'Méningocoque ACWY'
      WHEN dd.disease_id = '5de53894-adb3-4639-9ce1-0c12012d9ecf'
       AND dd.conclusion IN ('late','todo')
       AND dd.targeted_date < CURRENT_DATE
      THEN 'Rougeole'
      WHEN dd.disease_id = '648d9876-393c-4a35-9fe4-bb67af2f5573'
       AND dd.conclusion IN ('late','todo')
       AND dd.targeted_date < CURRENT_DATE
      THEN 'Oreillons'
      WHEN dd.disease_id = '5adf2b1f-8df9-4b71-8c43-e097da7d85a8'
       AND dd.conclusion IN ('late','todo')
       AND dd.targeted_date < CURRENT_DATE
      THEN 'Rubéole'
      WHEN dd.disease_id = '31f90c1a-da1c-4b2c-b894-ff797c2aff5c'
       AND dd.conclusion IN ('late','todo')
       AND dd.targeted_date < CURRENT_DATE
      THEN 'Grippe'
    END,
    ', '
    ORDER BY
      CASE
        WHEN dd.disease_id = '47cac099-7918-4329-9079-bdeb057957f0' THEN 1
        WHEN dd.disease_id = 'ae4a5fbe-9c4a-4836-abd0-da0247cadf42' THEN 2
        WHEN dd.disease_id = '943d1be1-e2c9-47db-b938-03eab0fac7b0' THEN 3
        WHEN dd.disease_id = '7cd78fe4-3e8e-4d2b-b9fb-7c0f6c57c8dc' THEN 4
        WHEN dd.disease_id = 'a3771c39-a733-459d-a3a3-31b3f2f6240a' THEN 5
        WHEN dd.disease_id = '3fe82df3-f5ae-4054-9f7c-0f2701aa6bfe' THEN 6
        WHEN dd.disease_id = '084a8856-671b-4335-a96e-a311f3fc4495' THEN 7
        WHEN dd.disease_id = '49722121-d39b-45b3-af76-8689e7927320' THEN 8
        WHEN dd.disease_id = '2476da72-6573-46c0-918e-d363f8337623' THEN 9
        WHEN dd.disease_id = '5de53894-adb3-4639-9ce1-0c12012d9ecf' THEN 10
        WHEN dd.disease_id = '648d9876-393c-4a35-9fe4-bb67af2f5573' THEN 11
        WHEN dd.disease_id = '5adf2b1f-8df9-4b71-8c43-e097da7d85a8' THEN 12
        WHEN dd.disease_id = '31f90c1a-da1c-4b2c-b894-ff797c2aff5c' THEN 13
      END
  ) AS "Maladies à faire"

FROM patients p
CROSS JOIN params
LEFT JOIN disease_diagnoses dd
  ON dd.patient_id = p.id
 AND dd.disease_id IN (
   '47cac099-7918-4329-9079-bdeb057957f0', -- Diphtérie
   'ae4a5fbe-9c4a-4836-abd0-da0247cadf42', -- Tétanos
   '943d1be1-e2c9-47db-b938-03eab0fac7b0', -- Polio
   '7cd78fe4-3e8e-4d2b-b9fb-7c0f6c57c8dc', -- Coqueluche
   'a3771c39-a733-459d-a3a3-31b3f2f6240a', -- Hib
   '3fe82df3-f5ae-4054-9f7c-0f2701aa6bfe', -- Hépatite B
   '084a8856-671b-4335-a96e-a311f3fc4495', -- Pneumocoque
   '49722121-d39b-45b3-af76-8689e7927320', -- Méningocoque B
   '2476da72-6573-46c0-918e-d363f8337623', -- Méningocoque W
   '5de53894-adb3-4639-9ce1-0c12012d9ecf', -- Rougeole
   '648d9876-393c-4a35-9fe4-bb67af2f5573', -- Oreillons
   '5adf2b1f-8df9-4b71-8c43-e097da7d85a8', -- Rubéole
   '31f90c1a-da1c-4b2c-b894-ff797c2aff5c'  -- Grippe saisonnière
 )

WHERE p.birth_date <= (CURRENT_DATE - INTERVAL '24 months')
  AND p.birth_date >  (CURRENT_DATE - INTERVAL '25 months')
  AND NOT EXISTS (
  SELECT 1
  FROM unnest(params.tags_exclus) AS t(tag)
  WHERE list_contains(
    list_transform(coalesce(p.tags, []), x -> lower(x)),
    lower(tag)
  )
)

GROUP BY
  p.last_name, p.first_names, p.gender, p.birth_date, p.created_at,
  p.address1, p.address2, p.city_zip_code, p.city, p.email, p.email_2,
  p.phone_number, p.phone_number_2
ORDER BY p.last_name, p.first_names;

Liste des enfants de 6 à 10 ans révolus à convoquer pour mise à jour des vaccinations contre : DTCaP, hépatite B, ROR et grippe saisonnière

  • Sélection des patients : oui
  • Récupération des carnets : oui
  • Récupération des diagnostics : non

Algorithme : 

Est convoqué pour une dose  de : 

  • DTPCa si n'a pas reçu 3 doses (ou dernière dose est un rappel) d'un vaccin au minimum pentavalent, avec une dernière dose à partir de l'âge de 5 ans ;
  • Hépatite B si n'a pas reçu 3 doses de vaccin contenant une valence hépatite B ;
  • ROR si n'a pas reçu 2 doses de vaccin ROR avec d1 à partir de 12 mois ou 3 doses ;
  • Grippe si n'a pas reçu 1 dose vaccinale depuis moins de 6 mois.

La dernière colonne indique les maladies pour lesquelles l'enfant doit être convoqué.

L'export Excel des résultats de cette requête est prêt pour une exploitation en publipostage (par voie postale, e-mail ou SMS, tous les champs nécessaires étant présents).

WITH params AS (
  SELECT
    3  			AS min_doses_dtpc,
    '5 years'  	AS min_age_dtpc,
    3  			AS min_doses_hepb,
    2  			AS min_doses_ror,
    '12 months' AS age_ror_reference,
    '2 weeks' 	AS marge_ror,          -- marge de tolérance (d1 après 11 mois et 2 sem)
    183 		AS delai_grippe_jours, -- 6 mois
    ['']        AS tags_exclus         -- Liste des tags à exclure
),

/* =========================
   DTPCoq
   ========================= */
dtpc_par_patient AS (
  WITH last_dtpc AS (
    SELECT DISTINCT ON (v.patient_id)
      v.patient_id,
      v.performed_on AS derniere_dtpc,
      v.booster      AS derniere_dtpc_est_booster
    FROM vaccinations v
    WHERE v.vaccine_id IN (
      '246a6067-7c45-42af-bff9-90832bb6e4aa',
      '3ddb5821-9a52-428e-a40d-d065e4a630b1',
      'b9562145-be50-420f-a809-dd0e2762d142',
      '3ac78420-22e7-4ef4-90de-979a17726312',
      'c7d9d700-b481-4072-ab16-3b1aa7376298',
      '1a6e8b3b-dff8-421b-8892-8790e343127a',
      '69f2e048-13fc-4848-8b91-39246baf2d93',
      '7fa97d99-ff52-41b8-b267-679d7b003620',
      'd51d40bc-3646-4dc3-8cb9-e810ce2e1029',
      '768562aa-706b-4d6a-91e2-f49fe9ad3b96',
      'd64aa9db-cb9e-4b55-997f-08a06cdcf87b',
      'fdac4922-a30a-48d0-88bd-7c16c872528f',
      'ffc5a77a-87e1-420d-80c9-69e0b3fb9474'
    )
    ORDER BY v.patient_id, v.performed_on DESC, v.id DESC
  )
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_dtpc,
    MAX(v.performed_on) AS derniere_dtpc,
    COALESCE(MAX(last_dtpc.derniere_dtpc_est_booster::int), 0)::boolean AS derniere_dtpc_est_booster
  FROM vaccinations v
  LEFT JOIN last_dtpc ON last_dtpc.patient_id = v.patient_id
  WHERE v.vaccine_id IN (
    '246a6067-7c45-42af-bff9-90832bb6e4aa',
    '3ddb5821-9a52-428e-a40d-d065e4a630b1',
    'b9562145-be50-420f-a809-dd0e2762d142',
    '3ac78420-22e7-4ef4-90de-979a17726312',
    'c7d9d700-b481-4072-ab16-3b1aa7376298',
    '1a6e8b3b-dff8-421b-8892-8790e343127a',
    '69f2e048-13fc-4848-8b91-39246baf2d93',
    '7fa97d99-ff52-41b8-b267-679d7b003620',
    'd51d40bc-3646-4dc3-8cb9-e810ce2e1029',
    '768562aa-706b-4d6a-91e2-f49fe9ad3b96',
    'd64aa9db-cb9e-4b55-997f-08a06cdcf87b',
    'fdac4922-a30a-48d0-88bd-7c16c872528f',
    'ffc5a77a-87e1-420d-80c9-69e0b3fb9474'
  )
  GROUP BY v.patient_id
),

/* =========================
   Hépatite B
   ========================= */
hepb_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_hepb
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA
    '53e85f4a-1d9a-47d1-bd1a-694292190cd7', -- INFANRIX PENTA
    '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON
    'b9562145-be50-420f-a809-dd0e2762d142', -- VAXELIS
    '3a350807-4143-4687-9ec7-2811d1a996c8', -- ENGERIX B 10 µg
    '304dca1e-358a-4721-a840-604320ea3587', -- HBVAXPRO 10 µg
    '04c8f3b5-67f5-43c1-9d9a-57792f8a6beb', -- HBVAXPRO 5 µg
    '7d9cbc35-b60f-4b60-96d0-0d9a79b9a3a0', -- GENEVAC-B 10 µg
    '77fab95c-0953-448a-b284-cd71b7a1583d', -- GENHEVAC B PASTEUR
    'ffc5a77a-87e1-420d-80c9-69e0b3fb9474', -- HEXAVAC
    'd64aa9db-cb9e-4b55-997f-08a06cdcf87b', -- Vaccin hexavalent DTPCoqHibHepB, sans précision
    '9696081d-d446-4b2a-b5f0-8bae932092ac'  -- Vaccin Hépatite B, sans précision
  )
  GROUP BY v.patient_id
),

/* =========================
   ROR (MMR)
   ========================= */
ror_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_ror,
    min(v.performed_on) AS premiere_ror
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '9435556b-4fce-44f7-81d6-e5d2d240860b', -- PRIORIX
    '9a08067b-3ec7-4229-a6f6-0803049ee699', -- MMR-VaxPro
    'd50a6151-d732-4232-953d-09d95e0c6c3b', -- Vaccin ROR, sans précision
    '45e9900f-591e-4e05-89b4-984cac1a0c61'  -- R.O.R. VAX
  )
  GROUP BY v.patient_id
),

/* =========================
   Grippe
   ========================= */
grippe_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_grippe,
    max(v.performed_on) AS derniere_grippe
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    'd5201c83-c7fe-48a4-b34c-f38e78f39cac', -- FLUCELVAX Trivalent
    '113c41b5-19c4-4788-811b-0fa81a18d636', -- INFLUVAC Trivalent
    '921a4957-762d-4ae4-ad70-bab26b28c5d2', -- VAXIGRIP Trivalent
    '9dd17477-32e9-4078-927c-90a7bd737628', -- VAXIGRIP ENFANTS Trivalent
    '7ae62689-ae48-41a1-afba-7d5a1e9f511d', -- EFLUELDA Trivalent
    'c39cdd50-8fec-40ef-8428-a05292f820d7'  -- FLUAD Trivalent
  )
  GROUP BY v.patient_id
),

patients_enrichis AS (
  SELECT
    p.id,
    p.last_name   AS Nom,
    p.first_names AS Prénom,
    p.gender      AS Sexe,
    p.birth_date  AS "Date de naissance",
    p.tags,
    p.created_at  AS "Création du dossier",
    p.address1 AS "Adresse",
    p.address2 AS "Complément d'adresse",
    p.city_zip_code AS "Code postal",
    p.city AS "Commune",
    p.email AS "Email",
    p.email_2 AS "Email secondaire",
    p.phone_number AS "Téléphone",
    p.phone_number_2 AS "Téléphone secondaire",

    /* âge pour filtre sur la sélection */
    date_diff('year', p.birth_date, current_date) AS age_ans,
	
	coalesce(d.nbre_doses_dtpc, 0) AS nbre_doses_dtpc,
	d.derniere_dtpc,
	coalesce(d.derniere_dtpc_est_booster, false) AS derniere_dtpc_est_booster,

    coalesce(h.nbre_doses_hepb, 0) AS nbre_doses_hepb,

    coalesce(r.nbre_doses_ror, 0) AS nbre_doses_ror,
    r.premiere_ror,

    coalesce(g.nbre_doses_grippe, 0) AS nbre_doses_grippe,
    g.derniere_grippe,

    params.min_doses_dtpc,
	params.min_age_dtpc,
    params.min_doses_hepb,
    params.min_doses_ror,
    params.delai_grippe_jours,
    params.tags_exclus,

    /* ===== Flags "à faire" ===== */
	(
	  d.derniere_dtpc IS NULL
	  OR d.derniere_dtpc < (p.birth_date + params.min_age_dtpc::interval)
	  OR (
	    coalesce(d.nbre_doses_dtpc, 0) < params.min_doses_dtpc
	    AND coalesce(d.derniere_dtpc_est_booster, false) = false
	  )
	) AS dtpcoq_a_faire,
	
    (coalesce(h.nbre_doses_hepb, 0) < params.min_doses_hepb) AS hepb_a_faire,
	
	(
	  coalesce(r.nbre_doses_ror, 0) < 2
	  OR (
	    coalesce(r.nbre_doses_ror, 0) < 3
	    AND r.premiere_ror IS NOT NULL
	    AND r.premiere_ror < (
	      p.birth_date
	      + params.age_ror_reference::interval
	      - params.marge_ror::interval
	    )
	  )
	) AS ror_a_faire,

    (
      g.derniere_grippe IS NULL
      OR date_diff('day', g.derniere_grippe, current_date) > params.delai_grippe_jours
    ) AS grippe_a_faire,
    
    params.tags_exclus

  FROM patients p
  CROSS JOIN params
  LEFT JOIN dtpc_par_patient d  ON d.patient_id = p.id
  LEFT JOIN hepb_par_patient h   ON h.patient_id = p.id
  LEFT JOIN ror_par_patient r    ON r.patient_id = p.id
  LEFT JOIN grippe_par_patient g ON g.patient_id = p.id
)

SELECT
  Nom,
  Prénom,
  Sexe,
  "Date de naissance",
  
  /* Âge lisible : années + mois (basé sur AGE) */
  EXTRACT(YEAR FROM AGE(CURRENT_DATE, "Date de naissance"))::int
  || 'a '
  || EXTRACT(MONTH FROM AGE(CURRENT_DATE, "Date de naissance"))::int
  || 'm'
  AS "Âge ce jour",
  
  "Adresse",
  "Complément d'adresse",
  "Code postal",
  "Commune",
  "Email",
  "Email secondaire",
  "Téléphone",
  "Téléphone secondaire",
  "Création du dossier",

  /* Détails vaccins */
  nbre_doses_dtpc	AS "Doses DTPCoq",
  CASE
    WHEN derniere_dtpc_est_booster THEN 'Oui'
    ELSE NULL
  END AS "Dernière DTPC = Booster",
  
  CASE
    WHEN derniere_dtpc IS NULL THEN NULL
    ELSE
      trim(
        BOTH ' ' FROM
          concat(
            CASE WHEN a.y > 0 THEN a.y || 'a ' ELSE '' END,
            CASE WHEN a.m > 0 THEN a.m || 'm ' ELSE '' END,
            a.d || 'j'
          )
      )
  END AS "Âge dd DTPCoq",

  nbre_doses_hepb  AS "Doses HVB",

  nbre_doses_ror   AS "Doses ROR",
  
  CASE
    WHEN premiere_ror IS NULL THEN NULL
    ELSE
      trim(
        BOTH ' ' FROM
          concat(
            CASE WHEN aror.y > 0 THEN aror.y || 'a ' ELSE '' END,
            CASE WHEN aror.m > 0 THEN aror.m || 'm ' ELSE '' END,
            aror.d || 'j'
          )
      )
  END AS "Âge d1 ROR",

  nbre_doses_grippe AS "Doses Grippe",
  derniere_grippe   AS "Dernière Grippe",

  /* Liste des vaccins à faire */
  array_to_string(
    list_filter(
      list_value(
        CASE WHEN dtpcoq_a_faire THEN 'DTPCoq' END,
        CASE WHEN hepb_a_faire   THEN 'Hépatite B' END,
        CASE WHEN ror_a_faire    THEN 'ROR' END,
        CASE WHEN grippe_a_faire THEN 'Grippe' END
      ),
      x -> x IS NOT NULL
    ),
    ', '
  ) AS "Vaccins à faire"

FROM patients_enrichis
LEFT JOIN LATERAL (
  SELECT
    EXTRACT(YEAR  FROM age(derniere_dtpc, "Date de naissance"))::int AS y,
    EXTRACT(MONTH FROM age(derniere_dtpc, "Date de naissance"))::int AS m,
    EXTRACT(DAY   FROM age(derniere_dtpc, "Date de naissance"))::int AS d
) a ON true
LEFT JOIN LATERAL (
  SELECT
    EXTRACT(YEAR  FROM age(premiere_ror, "Date de naissance"))::int AS y,
    EXTRACT(MONTH FROM age(premiere_ror, "Date de naissance"))::int AS m,
    EXTRACT(DAY   FROM age(premiere_ror, "Date de naissance"))::int AS d
) aror ON true

WHERE age_ans BETWEEN 6 AND 10
  AND NOT EXISTS (
  SELECT 1
  FROM unnest(patients_enrichis.tags_exclus) AS t(tag)
  WHERE list_contains(
    list_transform(coalesce(patients_enrichis.tags, []), x -> lower(x)),
    lower(tag)
    )
  )
ORDER BY Nom, Prénom;

Liste des adolescents de 11 à 14 ans révolus à convoquer pour mise à jour des vaccinations contre : DTCaP, hépatite B, ROR, méningocoques ACWY, papillomavirus et grippe saisonnière

  • Sélection des patients : oui
  • Récupération des carnets : oui
  • Récupération des diagnostics : non

Algorithme : 

Est convoqué pour une dose  de : 

  • DTPCa si n'a pas reçu 3 doses (ou dernière dose est un rappel) d'un vaccin au minimum pentavalent, avec une dernière dose à partir de l'âge de 10 ans et demi ;
  • Hépatite B si n'a pas reçu 3 doses de vaccin contenant une valence hépatite B ;
  • ROR si n'a pas reçu 2 doses de vaccin ROR avec d1 à partir de 12 mois ou 3 doses ;
  • ACWY si n'a pas reçu au moins une dose à partir de l'âge de 10 ans et demi ;
  • HPV si n'a pas reçu 2 doses espacées d'au moins 5 mois ou 3 doses ;
  • Grippe si n'a pas reçu 1 dose vaccinale depuis moins de 6 mois.

La dernière colonne indique les maladies pour lesquelles l'adolescent doit être convoqué.

L'export Excel des résultats de cette requête est prêt pour une exploitation en publipostage (par voie postale, e-mail ou SMS, tous les champs nécessaires étant présents).

WITH params AS (
  SELECT
    3                            AS min_doses_dtpc,
    INTERVAL '10 years 6 months' AS min_age_dtpc,
    INTERVAL '10 years 6 months' AS min_age_acwy,
    3                            AS min_doses_hepb,
    2                            AS min_doses_ror,
    INTERVAL '12 months'         AS age_ror_reference,
    INTERVAL '2 weeks'           AS marge_ror,          -- ROR : marge de tolérance (d1 après 11 mois et 2 sem)
    INTERVAL '5 months'          AS min_ecart_hpv,      -- HPV : écart mini entre dose1 et dose2
    183                          AS delai_grippe_jours, -- 6 mois
    [''] AS tags_exclus                                 -- Liste des tags à exclure
),

/* =========================
   Méningocoque ACWY
   ========================= */
acwy_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_acwy,
    MAX(v.performed_on) AS derniere_acwy
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '54754014-a3fc-42bb-b007-1a2024eca164', -- MENVEO
    '38fef5bc-4f68-4292-aa82-c45042ab4af4'  -- NIMENRIX
  )
  GROUP BY v.patient_id
),

/* =========================
   HPV (GARDASIL 9)
   ========================= */
hpv_par_patient AS (
  WITH hpv_doses AS (
    SELECT
      v.patient_id,
      v.performed_on,
      ROW_NUMBER() OVER (
        PARTITION BY v.patient_id
        ORDER BY v.performed_on ASC, v.id ASC
      ) AS rn
    FROM vaccinations v
    WHERE v.vaccine_id = 'a462bc91-c834-482c-b3ed-81f20eeabb64' -- GARDASIL 9
  )
  SELECT
    patient_id,
    COUNT(*) AS nbre_doses_hpv,
    MAX(CASE WHEN rn = 1 THEN performed_on END) AS d1_hpv,
    MAX(CASE WHEN rn = 2 THEN performed_on END) AS d2_hpv
  FROM hpv_doses
  GROUP BY patient_id
),

/* =========================
   DTPCoq
   ========================= */
dtpc_par_patient AS (
  WITH last_dtpc AS (
    SELECT
      v.patient_id,
      v.performed_on AS derniere_dtpc,
      v.booster      AS derniere_dtpc_est_booster
    FROM vaccinations v
    WHERE v.vaccine_id IN (
      '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA
      '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON
      'b9562145-be50-420f-a809-dd0e2762d142', -- VAXELIS
      '3ac78420-22e7-4ef4-90de-979a17726312', -- TETRAVAC-ACELLULAIRE
      'da117bfd-a4d3-4aec-9020-6a3cf1fa276a', -- INFANRIXQUINTA
      'c7d9d700-b481-4072-ab16-3b1aa7376298', -- INFANRIXTETRA
      '384198db-b13c-4e08-b7a1-9311809a21b9', -- BOOSTRIXTETRA
      'ef50c7de-8bcd-4525-b2a0-e1047c376d83', -- TETRACOQ
      '1a6e8b3b-dff8-421b-8892-8790e343127a', -- PENT-HIBEST
      '69f2e048-13fc-4848-8b91-39246baf2d93', -- PENTAVAC
      '7fa97d99-ff52-41b8-b267-679d7b003620', -- PENTACOQ
      '55adc664-d4a2-4154-80b2-dabd139fed28', -- REPEVAX
      'd51d40bc-3646-4dc3-8cb9-e810ce2e1029', -- DTP - Diphtérie-Tétanos-Polio, sans précision
      '768562aa-706b-4d6a-91e2-f49fe9ad3b96', -- Diphtérie-Tétanos-Polio, dose réduite, sans précision
      'd64aa9db-cb9e-4b55-997f-08a06cdcf87b', -- Vaccin hexavalent DTPCoqHibHepB, sans précision
      'fdac4922-a30a-48d0-88bd-7c16c872528f', -- Vaccin DiphTP - Diphtérie-Tétanos-Polio, sans précision
      'e3981dfe-99e5-4092-b073-5639d6761e08', -- Vaccin pentavalent DTCoqHibP - Diphtérie-Tétanos-Coqueluche-Hib-Polio, sans précision
      'c9e71f76-f164-4246-92ea-d750c128f199', -- Vaccin DiphTPCoq - Diphtérie-Tétanos-Coqueluche-Polio, sans précision
      'ffc5a77a-87e1-420d-80c9-69e0b3fb9474'  -- HEXAVAC
    )
    QUALIFY ROW_NUMBER() OVER (
      PARTITION BY v.patient_id
      ORDER BY v.performed_on DESC, v.id DESC
    ) = 1
  )
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_dtpc,
    MAX(v.performed_on) AS derniere_dtpc,
    COALESCE(ANY_VALUE(last_dtpc.derniere_dtpc_est_booster), false) AS derniere_dtpc_est_booster
  FROM vaccinations v
  LEFT JOIN last_dtpc ON last_dtpc.patient_id = v.patient_id
  WHERE v.vaccine_id IN (
      '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA
      '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON
      'b9562145-be50-420f-a809-dd0e2762d142', -- VAXELIS
      '3ac78420-22e7-4ef4-90de-979a17726312', -- TETRAVAC-ACELLULAIRE
      'da117bfd-a4d3-4aec-9020-6a3cf1fa276a', -- INFANRIXQUINTA
      'c7d9d700-b481-4072-ab16-3b1aa7376298', -- INFANRIXTETRA
      '384198db-b13c-4e08-b7a1-9311809a21b9', -- BOOSTRIXTETRA
      'ef50c7de-8bcd-4525-b2a0-e1047c376d83', -- TETRACOQ
      '1a6e8b3b-dff8-421b-8892-8790e343127a', -- PENT-HIBEST
      '69f2e048-13fc-4848-8b91-39246baf2d93', -- PENTAVAC
      '7fa97d99-ff52-41b8-b267-679d7b003620', -- PENTACOQ
      '55adc664-d4a2-4154-80b2-dabd139fed28', -- REPEVAX
      'd51d40bc-3646-4dc3-8cb9-e810ce2e1029', -- DTP - Diphtérie-Tétanos-Polio, sans précision
      '768562aa-706b-4d6a-91e2-f49fe9ad3b96', -- Diphtérie-Tétanos-Polio, dose réduite, sans précision
      'd64aa9db-cb9e-4b55-997f-08a06cdcf87b', -- Vaccin hexavalent DTPCoqHibHepB, sans précision
      'fdac4922-a30a-48d0-88bd-7c16c872528f', -- Vaccin DiphTP - Diphtérie-Tétanos-Polio, sans précision
      'e3981dfe-99e5-4092-b073-5639d6761e08', -- Vaccin pentavalent DTCoqHibP - Diphtérie-Tétanos-Coqueluche-Hib-Polio, sans précision
      'c9e71f76-f164-4246-92ea-d750c128f199', -- Vaccin DiphTPCoq - Diphtérie-Tétanos-Coqueluche-Polio, sans précision
      'ffc5a77a-87e1-420d-80c9-69e0b3fb9474'  -- HEXAVAC
  )
  GROUP BY v.patient_id
),

/* =========================
   Hépatite B
   ========================= */
hepb_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_hepb
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA
    '53e85f4a-1d9a-47d1-bd1a-694292190cd7', -- INFANRIX PENTA
    '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON
    'b9562145-be50-420f-a809-dd0e2762d142', -- VAXELIS
    '3a350807-4143-4687-9ec7-2811d1a996c8', -- ENGERIX B 10 µg
    '304dca1e-358a-4721-a840-604320ea3587', -- HBVAXPRO 10 µg
    '04c8f3b5-67f5-43c1-9d9a-57792f8a6beb', -- HBVAXPRO 5 µg
    '7d9cbc35-b60f-4b60-96d0-0d9a79b9a3a0', -- GENEVAC-B 10 µg
    '77fab95c-0953-448a-b284-cd71b7a1583d', -- GENHEVAC B PASTEUR
    'ffc5a77a-87e1-420d-80c9-69e0b3fb9474', -- HEXAVAC
    'd64aa9db-cb9e-4b55-997f-08a06cdcf87b', -- Vaccin hexavalent DTPCoqHibHepB, sans précision
    '9696081d-d446-4b2a-b5f0-8bae932092ac'  -- Vaccin Hépatite B, sans précision
  )
  GROUP BY v.patient_id
),

/* =========================
   ROR (MMR)
   ========================= */
ror_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_ror,
    MIN(v.performed_on) AS premiere_ror
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '9435556b-4fce-44f7-81d6-e5d2d240860b', -- PRIORIX
    '9a08067b-3ec7-4229-a6f6-0803049ee699', -- MMR-VaxPro
    'd50a6151-d732-4232-953d-09d95e0c6c3b', -- Vaccin ROR, sans précision
    '45e9900f-591e-4e05-89b4-984cac1a0c61'  -- R.O.R. VAX
  )
  GROUP BY v.patient_id
),

/* =========================
   Grippe
   ========================= */
grippe_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_grippe,
    MAX(v.performed_on) AS derniere_grippe
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    'd5201c83-c7fe-48a4-b34c-f38e78f39cac', -- FLUCELVAX Trivalent
    '113c41b5-19c4-4788-811b-0fa81a18d636', -- INFLUVAC Trivalent
    '921a4957-762d-4ae4-ad70-bab26b28c5d2', -- VAXIGRIP Trivalent
    '9dd17477-32e9-4078-927c-90a7bd737628', -- VAXIGRIP ENFANTS Trivalent
    '7ae62689-ae48-41a1-afba-7d5a1e9f511d', -- EFLUELDA Trivalent
    'c39cdd50-8fec-40ef-8428-a05292f820d7'  -- FLUAD Trivalent
  )
  GROUP BY v.patient_id
),

patients_enrichis AS (
  SELECT
    p.id,
    p.last_name   AS Nom,
    p.first_names AS "Prénom",
    p.gender      AS Sexe,
    p.birth_date  AS "Date de naissance",
    p.tags,
    p.created_at  AS "Création du dossier",
    p.address1 AS "Adresse",
    p.address2 AS "Complément d'adresse",
    p.city_zip_code AS "Code postal",
    p.city AS "Commune",
    p.email AS "Email",
    p.email_2 AS "Email secondaire",
    p.phone_number AS "Téléphone",
    p.phone_number_2 AS "Téléphone secondaire",

    /* âge pour filtre sur la sélection */
    date_diff('year', p.birth_date, current_date) AS age_ans,

    COALESCE(d.nbre_doses_dtpc, 0) AS nbre_doses_dtpc,
    d.derniere_dtpc,
    COALESCE(d.derniere_dtpc_est_booster, false) AS derniere_dtpc_est_booster,

    COALESCE(h.nbre_doses_hepb, 0) AS nbre_doses_hepb,

    COALESCE(r.nbre_doses_ror, 0) AS nbre_doses_ror,
    r.premiere_ror,

    COALESCE(g.nbre_doses_grippe, 0) AS nbre_doses_grippe,
    g.derniere_grippe,

    params.min_doses_dtpc,
    params.min_age_dtpc,
    params.min_age_acwy,
    params.min_ecart_hpv,
    params.min_doses_hepb,
    params.min_doses_ror,
    params.age_ror_reference,
    params.marge_ror,
    params.delai_grippe_jours,
    params.tags_exclus,

    /* ===== Flags "à faire" ===== */
    (
      d.derniere_dtpc IS NULL
      OR d.derniere_dtpc < (p.birth_date + params.min_age_dtpc)
      OR (
        COALESCE(d.nbre_doses_dtpc, 0) < params.min_doses_dtpc
        AND COALESCE(d.derniere_dtpc_est_booster, false) = false
      )
    ) AS dtpcoq_a_faire,

    (COALESCE(h.nbre_doses_hepb, 0) < params.min_doses_hepb) AS hepb_a_faire,

    (
      COALESCE(r.nbre_doses_ror, 0) < 2
      OR (
        COALESCE(r.nbre_doses_ror, 0) < 3
        AND r.premiere_ror IS NOT NULL
        AND r.premiere_ror < (
          p.birth_date
          + params.age_ror_reference
          - params.marge_ror
        )
      )
    ) AS ror_a_faire,

    COALESCE(acwy.nbre_doses_acwy, 0) AS nbre_doses_acwy,
    acwy.derniere_acwy,

    (
      COALESCE(acwy.nbre_doses_acwy, 0) = 0
      OR acwy.derniere_acwy IS NULL
      OR acwy.derniere_acwy < (p.birth_date + params.min_age_acwy)
    ) AS acwy_a_faire,

    COALESCE(hpv.nbre_doses_hpv, 0) AS nbre_doses_hpv,
    hpv.d1_hpv,
    hpv.d2_hpv,

    (
      COALESCE(hpv.nbre_doses_hpv, 0) < 2
      OR (
        COALESCE(hpv.nbre_doses_hpv, 0) = 2
        AND (
          hpv.d1_hpv IS NULL
          OR hpv.d2_hpv IS NULL
          OR hpv.d2_hpv < (hpv.d1_hpv + params.min_ecart_hpv)
        )
      )
    ) AS hpv_a_faire,

    (
      g.derniere_grippe IS NULL
      OR date_diff('day', g.derniere_grippe, current_date) > params.delai_grippe_jours
    ) AS grippe_a_faire,
    
    params.tags_exclus

  FROM patients p
  CROSS JOIN params
  LEFT JOIN dtpc_par_patient d    ON d.patient_id = p.id
  LEFT JOIN hepb_par_patient h    ON h.patient_id = p.id
  LEFT JOIN ror_par_patient r     ON r.patient_id = p.id
  LEFT JOIN grippe_par_patient g  ON g.patient_id = p.id
  LEFT JOIN acwy_par_patient acwy ON acwy.patient_id = p.id
  LEFT JOIN hpv_par_patient hpv   ON hpv.patient_id = p.id
)

SELECT
  Nom,
  "Prénom",
  Sexe,
  "Date de naissance",

  /* Âge lisible : années + mois (calcul DuckDB) */
  (
    CAST(date_diff('month', "Date de naissance", current_date) / 12 AS INTEGER) || 'a '
    || CAST(date_diff('month', "Date de naissance", current_date) % 12 AS INTEGER) || 'm'
  ) AS "Âge ce jour",

  "Adresse",
  "Complément d'adresse",
  "Code postal",
  "Commune",
  "Email",
  "Email secondaire",
  "Téléphone",
  "Téléphone secondaire",
  "Création du dossier",

  /* Détails vaccins */
  nbre_doses_dtpc AS "Doses DTPCoq",
  CASE
    WHEN derniere_dtpc_est_booster THEN 'Oui'
    ELSE NULL
  END AS "dd DTPCoq = Booster",

  CASE
    WHEN derniere_dtpc IS NULL THEN NULL
    ELSE
      trim(
        BOTH ' ' FROM
          concat(
            CASE WHEN a.y > 0 THEN a.y || 'a ' ELSE '' END,
            CASE WHEN a.m > 0 THEN a.m || 'm ' ELSE '' END,
            a.d || 'j'
          )
      )
  END AS "Âge dd DTPCoq",

  nbre_doses_hepb AS "Doses HVB",

  nbre_doses_ror  AS "Doses ROR",

  CASE
    WHEN premiere_ror IS NULL THEN NULL
    ELSE
      trim(
        BOTH ' ' FROM
          concat(
            CASE WHEN aror.y > 0 THEN aror.y || 'a ' ELSE '' END,
            CASE WHEN aror.m > 0 THEN aror.m || 'm ' ELSE '' END,
            aror.d || 'j'
          )
      )
  END AS "Âge d1 ROR",

  nbre_doses_acwy AS "Doses ACWY",
  CASE
    WHEN derniere_acwy IS NULL THEN NULL
    ELSE
      trim(
        BOTH ' ' FROM
          concat(
            CASE WHEN aacwy.y > 0 THEN aacwy.y || 'a ' ELSE '' END,
            CASE WHEN aacwy.m > 0 THEN aacwy.m || 'm ' ELSE '' END,
            aacwy.d || 'j'
          )
      )
  END AS "Âge dd ACWY",

  nbre_doses_hpv AS "Doses HPV",
  CASE
    WHEN d1_hpv IS NULL OR d2_hpv IS NULL THEN NULL
    ELSE
      (
        CASE
          WHEN date_diff('month', d1_hpv, d2_hpv) > 0
            THEN CAST(date_diff('month', d1_hpv, d2_hpv) AS INTEGER) || 'm '
          ELSE ''
        END
        ||
        CAST(
          date_diff(
            'day',
            d1_hpv + CAST(date_diff('month', d1_hpv, d2_hpv) AS INTEGER) * INTERVAL '1 month',
            d2_hpv
          ) AS INTEGER
        ) || 'j'
      )
  END AS "Écart d1d2",

  nbre_doses_grippe AS "Doses Grippe",
  derniere_grippe   AS "Dernière Grippe",

  /* Liste des vaccins à faire */
  array_to_string(
    list_filter(
      list_value(
        CASE WHEN dtpcoq_a_faire THEN 'DTCaP' END,
        CASE WHEN hepb_a_faire   THEN 'Hépatite B' END,
        CASE WHEN ror_a_faire    THEN 'ROR' END,
        CASE WHEN acwy_a_faire   THEN 'Men ACWY' END,
        CASE WHEN hpv_a_faire    THEN 'HPV' END,
        CASE WHEN grippe_a_faire THEN 'Grippe' END
      ),
      x -> x IS NOT NULL
    ),
    ', '
  ) AS "Vaccins à faire"

FROM patients_enrichis

/* Âge dd DTPCoq : y/m/d (DuckDB) */
LEFT JOIN LATERAL (
  SELECT
    CAST(date_diff('month', "Date de naissance", derniere_dtpc) / 12 AS INTEGER) AS y,
    CAST(date_diff('month', "Date de naissance", derniere_dtpc) % 12 AS INTEGER) AS m,
    CAST(
      date_diff(
        'day',
        "Date de naissance"
          + CAST(date_diff('month', "Date de naissance", derniere_dtpc) / 12 AS INTEGER) * INTERVAL '1 year'
          + CAST(date_diff('month', "Date de naissance", derniere_dtpc) % 12 AS INTEGER) * INTERVAL '1 month',
        derniere_dtpc
      ) AS INTEGER
    ) AS d
) a ON true

/* Âge d1 ROR : y/m/d (DuckDB) */
LEFT JOIN LATERAL (
  SELECT
    CAST(date_diff('month', "Date de naissance", premiere_ror) / 12 AS INTEGER) AS y,
    CAST(date_diff('month', "Date de naissance", premiere_ror) % 12 AS INTEGER) AS m,
    CAST(
      date_diff(
        'day',
        "Date de naissance"
          + CAST(date_diff('month', "Date de naissance", premiere_ror) / 12 AS INTEGER) * INTERVAL '1 year'
          + CAST(date_diff('month', "Date de naissance", premiere_ror) % 12 AS INTEGER) * INTERVAL '1 month',
        premiere_ror
      ) AS INTEGER
    ) AS d
) aror ON true

/* Âge dd ACWY : y/m/d (DuckDB) */
LEFT JOIN LATERAL (
  SELECT
    CAST(date_diff('month', "Date de naissance", derniere_acwy) / 12 AS INTEGER) AS y,
    CAST(date_diff('month', "Date de naissance", derniere_acwy) % 12 AS INTEGER) AS m,
    CAST(
      date_diff(
        'day',
        "Date de naissance"
          + CAST(date_diff('month', "Date de naissance", derniere_acwy) / 12 AS INTEGER) * INTERVAL '1 year'
          + CAST(date_diff('month', "Date de naissance", derniere_acwy) % 12 AS INTEGER) * INTERVAL '1 month',
        derniere_acwy
      ) AS INTEGER
    ) AS d
) aacwy ON true

WHERE age_ans BETWEEN 11 AND 14
  AND NOT EXISTS (
  SELECT 1
  FROM unnest(patients_enrichis.tags_exclus) AS t(tag)
  WHERE list_contains(
    list_transform(coalesce(patients_enrichis.tags, []), x -> lower(x)),
    lower(tag)
    )
  )
ORDER BY Nom, "Prénom";

Liste des adolescents et jeunes adultes de 15 à 24 ans révolus à convoquer pour mise à jour des vaccinations contre : méningocoques ACWY et B, ROR, papillomavirus et grippe saisonnière

  • Sélection des patients : oui
  • Récupération des carnets : oui
  • Récupération des diagnostics : non

Algorithme : 

Est convoqué pour une dose  de : 

  • ROR si n'a pas reçu 2 doses de vaccin ROR avec d1 à partir de 12 mois ou 3 doses ;
  • ACWY si n'a pas reçu au moins une dose à partir de l'âge de 10 ans et demi ;
  • Si Trumenba : 2 doses espacées ≥ 6 mois, ou schéma 3 doses (1 mois puis 4 mois après la 2e).
    Si Bexsero/NSP : 2 doses espacées ≥ 1 mois ;
    Le vaccin est “à faire” si le nombre de doses est insuffisant ou si les intervalles minimums ne sont pas respectés.
  • HPV si n'a pas reçu 2 doses espacées d'au moins 5 mois (d1 avant 15 ans) ou 3 doses si d1 à partir de 15 ans ;
  • Grippe si n'a pas reçu 1 dose vaccinale depuis moins de 6 mois.

La dernière colonne indique les maladies pour lesquelles le patient doit être convoqué.

L'export Excel des résultats de cette requête est prêt pour une exploitation en publipostage (par voie postale, e-mail ou SMS, tous les champs nécessaires étant présents).

WITH params AS (
  SELECT
    INTERVAL '10 years 6 months' AS min_age_acwy,
    2                            AS min_doses_ror,
    INTERVAL '12 months'         AS age_ror_reference,
    INTERVAL '2 weeks'           AS marge_ror,            -- tolérance ROR
    INTERVAL '5 months'          AS min_ecart_hpv,        -- écart mini d1->d2 HPV
    183                          AS delai_grippe_jours,   -- 6 mois (approx)
    [''] AS tags_exclus                                   -- Liste des tags à exclure
),

/* =========================
   Méningocoque ACWY
   ========================= */
acwy_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_acwy,
    MAX(v.performed_on) AS derniere_acwy
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '54754014-a3fc-42bb-b007-1a2024eca164', -- MENVEO
    '38fef5bc-4f68-4292-aa82-c45042ab4af4'  -- NIMENRIX
  )
  GROUP BY v.patient_id
),

/* =========================
   Méningocoque B (MenB)
   ========================= */
menb_par_patient AS (
  WITH menb_doses AS (
    SELECT
      v.patient_id,
      v.vaccine_id,
      v.performed_on,
      ROW_NUMBER() OVER (
        PARTITION BY v.patient_id
        ORDER BY v.performed_on ASC, v.id ASC
      ) AS rn
    FROM vaccinations v
    WHERE v.vaccine_id IN (
      '920c7a42-e375-449c-8f5c-4212c329379f', -- BEXSERO
      '2ed42ad7-87a8-46f8-a573-bfa595419237', -- MenB 4 composants, sans précision
      '268acc00-6c5c-4cda-a691-e0fb3eab2c1b', -- MenB, sans précision
      'e7e4e464-0689-4a26-918b-ed3a4a9a3de5'  -- TRUMENBA
    )
  )
  SELECT
    patient_id,
    COUNT(*) AS nbre_doses_menb,
    MAX(CASE WHEN rn = 1 THEN vaccine_id   END) AS vaccin_d1_menb,
    MAX(CASE WHEN rn = 1 THEN performed_on END) AS d1_menb,
    MAX(CASE WHEN rn = 2 THEN performed_on END) AS d2_menb,
    MAX(CASE WHEN rn = 3 THEN performed_on END) AS d3_menb,
    MAX(performed_on) AS derniere_menb
  FROM menb_doses
  GROUP BY patient_id
),

/* =========================
   HPV (GARDASIL 9)
   ========================= */
hpv_par_patient AS (
  WITH hpv_doses AS (
    SELECT
      v.patient_id,
      v.performed_on,
      ROW_NUMBER() OVER (
        PARTITION BY v.patient_id
        ORDER BY v.performed_on ASC, v.id ASC
      ) AS rn
    FROM vaccinations v
    WHERE v.vaccine_id = 'a462bc91-c834-482c-b3ed-81f20eeabb64'
  )
  SELECT
    patient_id,
    COUNT(*) AS nbre_doses_hpv,
    MAX(CASE WHEN rn = 1 THEN performed_on END) AS d1_hpv,
    MAX(CASE WHEN rn = 2 THEN performed_on END) AS d2_hpv,
    MAX(CASE WHEN rn = 3 THEN performed_on END) AS d3_hpv
  FROM hpv_doses
  GROUP BY patient_id
),

/* =========================
   ROR (MMR)
   ========================= */
ror_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_ror,
    MIN(v.performed_on) AS premiere_ror
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '9435556b-4fce-44f7-81d6-e5d2d240860b', -- PRIORIX
    '9a08067b-3ec7-4229-a6f6-0803049ee699', -- MMR-VaxPro
    'd50a6151-d732-4232-953d-09d95e0c6c3b', -- Vaccin ROR, sans précision
    '45e9900f-591e-4e05-89b4-984cac1a0c61'  -- R.O.R. VAX
  )
  GROUP BY v.patient_id
),

/* =========================
   Grippe
   ========================= */
grippe_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_grippe,
    MAX(v.performed_on) AS derniere_grippe
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    'd5201c83-c7fe-48a4-b34c-f38e78f39cac', -- FLUCELVAX Trivalent
    '113c41b5-19c4-4788-811b-0fa81a18d636', -- INFLUVAC Trivalent
    '921a4957-762d-4ae4-ad70-bab26b28c5d2', -- VAXIGRIP Trivalent
    '9dd17477-32e9-4078-927c-90a7bd737628', -- VAXIGRIP ENFANTS Trivalent
    '7ae62689-ae48-41a1-afba-7d5a1e9f511d', -- EFLUELDA Trivalent
    'c39cdd50-8fec-40ef-8428-a05292f820d7'  -- FLUAD Trivalent
  )
  GROUP BY v.patient_id
),

patients_enrichis AS (
  SELECT
    p.id,
    p.last_name   AS Nom,
    p.first_names AS "Prénom",
    p.gender      AS Sexe,
    p.birth_date  AS "Date de naissance",
    p.tags,
    p.created_at  AS "Création du dossier",
    p.address1    AS "Adresse",
    p.address2    AS "Complément d'adresse",
    p.city_zip_code AS "Code postal",
    p.city        AS "Commune",
    p.email       AS "Email",
    p.email_2     AS "Email secondaire",
    p.phone_number   AS "Téléphone",
    p.phone_number_2 AS "Téléphone secondaire",

    /* âge pour filtre */
    date_diff('year', p.birth_date, current_date) AS age_ans,

    COALESCE(r.nbre_doses_ror, 0) AS nbre_doses_ror,
    r.premiere_ror,

    COALESCE(g.nbre_doses_grippe, 0) AS nbre_doses_grippe,
    g.derniere_grippe,

    COALESCE(acwy.nbre_doses_acwy, 0) AS nbre_doses_acwy,
    acwy.derniere_acwy,

    COALESCE(menb.nbre_doses_menb, 0) AS nbre_doses_menb,
    menb.vaccin_d1_menb,
    menb.d1_menb,
    menb.d2_menb,
    menb.d3_menb,
    menb.derniere_menb,

    COALESCE(hpv.nbre_doses_hpv, 0) AS nbre_doses_hpv,
    hpv.d1_hpv,
    hpv.d2_hpv,
    hpv.d3_hpv,

    CASE
      WHEN hpv.d1_hpv IS NULL THEN 2
      WHEN hpv.d1_hpv < (p.birth_date + INTERVAL '15 years') THEN 2
      ELSE 3
    END AS hpv_doses_requises,

    /* ===== Flags "à faire" ===== */

    (
      COALESCE(r.nbre_doses_ror, 0) < 2
      OR (
        COALESCE(r.nbre_doses_ror, 0) < 3
        AND r.premiere_ror IS NOT NULL
        AND r.premiere_ror < (
          p.birth_date
          + params.age_ror_reference
          - params.marge_ror
        )
      )
    ) AS ror_a_faire,

    (
      COALESCE(acwy.nbre_doses_acwy, 0) = 0
      OR acwy.derniere_acwy IS NULL
      OR acwy.derniere_acwy < (p.birth_date + params.min_age_acwy)
    ) AS acwy_a_faire,

    (
      /* MenB : schéma selon vaccin de la 1ère dose */
      COALESCE(menb.nbre_doses_menb, 0) = 0

      OR (
        menb.vaccin_d1_menb = 'e7e4e464-0689-4a26-918b-ed3a4a9a3de5' -- TRUMENBA
        AND (
          COALESCE(menb.nbre_doses_menb, 0) = 1
          OR (
            COALESCE(menb.nbre_doses_menb, 0) = 2
            AND (
              menb.d2_menb IS NULL
              OR menb.d2_menb < (menb.d1_menb + INTERVAL '6 months')
            )
          )
          OR (
            COALESCE(menb.nbre_doses_menb, 0) >= 3
            AND (
              menb.d2_menb IS NULL OR menb.d2_menb < (menb.d1_menb + INTERVAL '1 month')
              OR menb.d3_menb IS NULL OR menb.d3_menb < (menb.d2_menb + INTERVAL '4 months')
            )
          )
        )
      )

      OR (
        menb.vaccin_d1_menb IS NOT NULL
        AND menb.vaccin_d1_menb <> 'e7e4e464-0689-4a26-918b-ed3a4a9a3de5' -- pas Trumenba
        AND (
          COALESCE(menb.nbre_doses_menb, 0) < 2
          OR menb.d2_menb IS NULL
          OR menb.d2_menb < (menb.d1_menb + INTERVAL '1 month')
        )
      )
    ) AS menb_a_faire,

    (
      -- Si on n'a pas d1, on retombe sur la logique "moins de 2 doses" => à faire
      COALESCE(hpv.nbre_doses_hpv, 0) < (
        CASE
          WHEN hpv.d1_hpv IS NULL THEN 2
          WHEN hpv.d1_hpv < (p.birth_date + INTERVAL '15 years') THEN 2
          ELSE 3
        END
      )

      OR (
        -- Cas schéma 2 doses : vérifier fenêtre 5 à 13 mois entre d1 et d2
        (hpv.d1_hpv IS NOT NULL AND hpv.d1_hpv < (p.birth_date + INTERVAL '15 years'))
        AND (
          hpv.d2_hpv IS NULL
          OR hpv.d2_hpv < (hpv.d1_hpv + INTERVAL '5 months')
          OR hpv.d2_hpv > (hpv.d1_hpv + INTERVAL '13 months')
        )
      )

      OR (
        -- Cas schéma 3 doses : vérifier 0-2-6 mois (contrôles "minimums")
        (hpv.d1_hpv IS NOT NULL AND hpv.d1_hpv >= (p.birth_date + INTERVAL '15 years'))
        AND (
          hpv.d2_hpv IS NULL
          OR hpv.d3_hpv IS NULL
          OR hpv.d2_hpv < (hpv.d1_hpv + INTERVAL '2 months')
          OR hpv.d3_hpv < (hpv.d1_hpv + INTERVAL '6 months')
        )
      )
    ) AS hpv_a_faire,

    (
      g.derniere_grippe IS NULL
      OR date_diff('day', g.derniere_grippe, current_date) > params.delai_grippe_jours
    ) AS grippe_a_faire,
    
    params.tags_exclus

  FROM patients p
  CROSS JOIN params
  LEFT JOIN ror_par_patient r       ON r.patient_id = p.id
  LEFT JOIN grippe_par_patient g    ON g.patient_id = p.id
  LEFT JOIN acwy_par_patient acwy   ON acwy.patient_id = p.id
  LEFT JOIN menb_par_patient menb   ON menb.patient_id = p.id
  LEFT JOIN hpv_par_patient hpv     ON hpv.patient_id = p.id
)

SELECT
  Nom,
  "Prénom",
  Sexe,
  "Date de naissance",

  /* Âge lisible : années + mois */
  extract(year from age(current_date, "Date de naissance"))::int
  || 'a '
  || extract(month from age(current_date, "Date de naissance"))::int
  || 'm' AS "Âge ce jour",

  "Adresse",
  "Complément d'adresse",
  "Code postal",
  "Commune",
  "Email",
  "Email secondaire",
  "Téléphone",
  "Téléphone secondaire",
  "Création du dossier",

  /* Détails vaccins */
  nbre_doses_ror AS "Doses ROR",

  CASE
    WHEN premiere_ror IS NULL THEN NULL
    ELSE trim(BOTH ' ' FROM
      concat(
        CASE WHEN extract(year  from age(premiere_ror, "Date de naissance"))::int > 0
             THEN extract(year  from age(premiere_ror, "Date de naissance"))::int || 'a ' ELSE '' END,
        CASE WHEN extract(month from age(premiere_ror, "Date de naissance"))::int > 0
             THEN extract(month from age(premiere_ror, "Date de naissance"))::int || 'm ' ELSE '' END,
        extract(day from age(premiere_ror, "Date de naissance"))::int || 'j'
      )
    )
  END AS "Âge d1 ROR",

  nbre_doses_acwy AS "Doses ACWY",

  CASE
    WHEN derniere_acwy IS NULL THEN NULL
    ELSE trim(BOTH ' ' FROM
      concat(
        CASE WHEN extract(year  from age(derniere_acwy, "Date de naissance"))::int > 0
             THEN extract(year  from age(derniere_acwy, "Date de naissance"))::int || 'a ' ELSE '' END,
        CASE WHEN extract(month from age(derniere_acwy, "Date de naissance"))::int > 0
             THEN extract(month from age(derniere_acwy, "Date de naissance"))::int || 'm ' ELSE '' END,
        extract(day from age(derniere_acwy, "Date de naissance"))::int || 'j'
      )
    )
  END AS "Âge dd ACWY",

  nbre_doses_menb AS "Doses MenB",

  CASE
    WHEN d1_menb IS NULL THEN NULL
    ELSE
      trim(BOTH ' ' FROM
        concat(
          CASE WHEN extract(year  from age(d1_menb, "Date de naissance"))::int > 0
               THEN extract(year  from age(d1_menb, "Date de naissance"))::int || 'a ' ELSE '' END,
          CASE WHEN extract(month from age(d1_menb, "Date de naissance"))::int > 0
               THEN extract(month from age(d1_menb, "Date de naissance"))::int || 'm ' ELSE '' END,
          extract(day from age(d1_menb, "Date de naissance"))::int || 'j'
        )
      )
  END AS "Âge d1 MenB",

  derniere_menb AS "Dernière MenB",

  nbre_doses_hpv AS "Doses HPV",

  CASE
    WHEN d1_hpv IS NULL THEN NULL
    ELSE
      trim(
        BOTH ' ' FROM
          concat(
            CASE WHEN extract(year  from age(d1_hpv, "Date de naissance"))::int > 0
                 THEN extract(year  from age(d1_hpv, "Date de naissance"))::int || 'a ' ELSE '' END,
            CASE WHEN extract(month from age(d1_hpv, "Date de naissance"))::int > 0
                 THEN extract(month from age(d1_hpv, "Date de naissance"))::int || 'm ' ELSE '' END,
            extract(day from age(d1_hpv, "Date de naissance"))::int || 'j'
          )
      )
  END AS "Âge d1 HPV",

  CASE
    WHEN d1_hpv IS NULL OR d2_hpv IS NULL THEN NULL
    ELSE trim(BOTH ' ' FROM
      concat(
        CASE
          WHEN extract(month from age(d2_hpv, d1_hpv))::int > 0
          THEN extract(month from age(d2_hpv, d1_hpv))::int || 'm '
          ELSE ''
        END,
        extract(day from age(d2_hpv, d1_hpv))::int || 'j'
      )
    )
  END AS "Écart d1d2",

  nbre_doses_grippe AS "Doses Grippe",
  derniere_grippe   AS "Dernière Grippe",

  /* Liste des vaccins à faire */
  array_to_string(
    list_filter(
      list_value(
        CASE WHEN ror_a_faire    THEN 'ROR' END,
        CASE WHEN acwy_a_faire   THEN 'Men ACWY' END,
        CASE WHEN menb_a_faire   THEN 'Men B' END,
        CASE WHEN hpv_a_faire    THEN 'HPV' END,
        CASE WHEN grippe_a_faire THEN 'Grippe' END
      ),
      x -> x IS NOT NULL
    ),
    ', '
  ) AS "Vaccins à faire"

FROM patients_enrichis

WHERE age_ans BETWEEN 15 AND 24
  AND NOT EXISTS (
  SELECT 1
  FROM unnest(patients_enrichis.tags_exclus) AS t(tag)
  WHERE list_contains(
    list_transform(coalesce(patients_enrichis.tags, []), x -> lower(x)),
    lower(tag)
    )
  )
  
ORDER BY Nom, "Prénom";

Liste des adultes de 25 à 26 ans révolus à convoquer pour mise à jour des vaccinations contre : dtcaP, ROR, papillomavirus et grippe saisonnière

  • Sélection des patients : oui
  • Récupération des carnets : oui
  • Récupération des diagnostics : non

Algorithme : 

Est convoqué pour une dose  de : 

  • DTP si n'a pas reçu 3 doses (ou dernière dose est un rappel) d'un vaccin au minimum pentavalent, avec une dernière dose à partir de l'âge de 20 ans ;
  • Coqueluche si n'a pas reçu au moins 1 dose à partir de l'âge de 20 ans ;
  • ROR si n'a pas reçu 2 doses de vaccin ROR avec d1 à partir de 12 mois ou 3 doses ;
  • HPV si n'a pas reçu 2 doses espacées d'au moins 5 mois ou 3 doses ;
  • Grippe si n'a pas reçu 1 dose vaccinale depuis mois de 6 mois.

La dernière colonne indique les maladies pour lesquelles le patient doit être convoqué.

L'export Excel des résultats de cette requête est prêt pour une exploitation en publipostage (par voie postale, e-mail ou SMS, tous les champs nécessaires étant présents).

WITH params AS (
  SELECT
    3                    AS min_doses_dtp,
    INTERVAL '20 years'  AS min_age_dtp,
    INTERVAL '20 years'  AS min_age_coq,
    2                    AS min_doses_ror,
    INTERVAL '12 months' AS age_ror_reference,
    INTERVAL '2 weeks'   AS marge_ror,
    INTERVAL '5 months'  AS min_ecart_hpv,
    183                  AS delai_grippe_jours,
    ['']                 AS tags_exclus          -- Liste des tags à exclure
),

/* =========================
   DTP
   ========================= */
dtpc_par_patient AS (
  WITH last_dtp AS (
    SELECT DISTINCT ON (v.patient_id)
      v.patient_id,
      v.performed_on AS derniere_dtp,
      v.booster      AS derniere_dtp_est_booster
    FROM vaccinations v
    WHERE v.vaccine_id IN (
      '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA
      '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON
      'b9562145-be50-420f-a809-dd0e2762d142', -- VAXELIS
      '3ac78420-22e7-4ef4-90de-979a17726312', -- TETRAVAC-ACELLULAIRE
      'da117bfd-a4d3-4aec-9020-6a3cf1fa276a', -- INFANRIXQUINTA
      'c7d9d700-b481-4072-ab16-3b1aa7376298', -- INFANRIXTETRA
      '384198db-b13c-4e08-b7a1-9311809a21b9', -- BOOSTRIXTETRA
      'ef50c7de-8bcd-4525-b2a0-e1047c376d83', -- TETRACOQ
      '1a6e8b3b-dff8-421b-8892-8790e343127a', -- PENT-HIBEST
      '69f2e048-13fc-4848-8b91-39246baf2d93', -- PENTAVAC
      '7fa97d99-ff52-41b8-b267-679d7b003620', -- PENTACOQ
      '55adc664-d4a2-4154-80b2-dabd139fed28', -- REPEVAX
      'd51d40bc-3646-4dc3-8cb9-e810ce2e1029', -- DTP - Diphtérie-Tétanos-Polio, sans précision
      '768562aa-706b-4d6a-91e2-f49fe9ad3b96', -- Diphtérie-Tétanos-Polio, dose réduite, sans précision
      'd64aa9db-cb9e-4b55-997f-08a06cdcf87b', -- Vaccin hexavalent DTPCoqHibHepB, sans précision
      'fdac4922-a30a-48d0-88bd-7c16c872528f', -- Vaccin DiphTP - Diphtérie-Tétanos-Polio, sans précision
      'e3981dfe-99e5-4092-b073-5639d6761e08', -- Vaccin pentavalent DTCoqHibP - Diphtérie-Tétanos-Coqueluche-Hib-Polio, sans précision
      'c9e71f76-f164-4246-92ea-d750c128f199', -- Vaccin DiphTPCoq - Diphtérie-Tétanos-Coqueluche-Polio, sans précision
      'ffc5a77a-87e1-420d-80c9-69e0b3fb9474', -- HEXAVAC
      '55adc664-d4a2-4154-80b2-dabd139fed28'  -- REPEVAX
    )
    ORDER BY v.patient_id, v.performed_on DESC, v.id DESC
  )
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_dtp,
    MAX(v.performed_on) AS derniere_dtp,
    COALESCE(MAX(last_dtp.derniere_dtp_est_booster::int), 0)::boolean AS derniere_dtp_est_booster
  FROM vaccinations v
  LEFT JOIN last_dtp ON last_dtp.patient_id = v.patient_id
  WHERE v.vaccine_id IN (
      '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA
      '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON
      'b9562145-be50-420f-a809-dd0e2762d142', -- VAXELIS
      '3ac78420-22e7-4ef4-90de-979a17726312', -- TETRAVAC-ACELLULAIRE
      'da117bfd-a4d3-4aec-9020-6a3cf1fa276a', -- INFANRIXQUINTA
      'c7d9d700-b481-4072-ab16-3b1aa7376298', -- INFANRIXTETRA
      '384198db-b13c-4e08-b7a1-9311809a21b9', -- BOOSTRIXTETRA
      'ef50c7de-8bcd-4525-b2a0-e1047c376d83', -- TETRACOQ
      '1a6e8b3b-dff8-421b-8892-8790e343127a', -- PENT-HIBEST
      '69f2e048-13fc-4848-8b91-39246baf2d93', -- PENTAVAC
      '7fa97d99-ff52-41b8-b267-679d7b003620', -- PENTACOQ
      '55adc664-d4a2-4154-80b2-dabd139fed28', -- REPEVAX
      'd51d40bc-3646-4dc3-8cb9-e810ce2e1029', -- DTP - Diphtérie-Tétanos-Polio, sans précision
      '768562aa-706b-4d6a-91e2-f49fe9ad3b96', -- Diphtérie-Tétanos-Polio, dose réduite, sans précision
      'd64aa9db-cb9e-4b55-997f-08a06cdcf87b', -- Vaccin hexavalent DTPCoqHibHepB, sans précision
      'fdac4922-a30a-48d0-88bd-7c16c872528f', -- Vaccin DiphTP - Diphtérie-Tétanos-Polio, sans précision
      'e3981dfe-99e5-4092-b073-5639d6761e08', -- Vaccin pentavalent DTCoqHibP - Diphtérie-Tétanos-Coqueluche-Hib-Polio, sans précision
      'c9e71f76-f164-4246-92ea-d750c128f199', -- Vaccin DiphTPCoq - Diphtérie-Tétanos-Coqueluche-Polio, sans précision
      'ffc5a77a-87e1-420d-80c9-69e0b3fb9474', -- HEXAVAC
      '55adc664-d4a2-4154-80b2-dabd139fed28'  -- REPEVAX
  )
  GROUP BY v.patient_id
),

/* =========================
   Coqueluche
   ========================= */
coq_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_coq,
    MAX(v.performed_on) AS derniere_coq
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '384198db-b13c-4e08-b7a1-9311809a21b9', -- BOOSTRIXTETRA
    '55adc664-d4a2-4154-80b2-dabd139fed28'  -- REPEVAX
  )
  GROUP BY v.patient_id
),

/* =========================
   HPV
   ========================= */
hpv_par_patient AS (
  WITH hpv_doses AS (
    SELECT
      v.patient_id,
      v.performed_on,
      ROW_NUMBER() OVER (
        PARTITION BY v.patient_id
        ORDER BY v.performed_on ASC, v.id ASC
      ) AS rn
    FROM vaccinations v
    WHERE v.vaccine_id = 'a462bc91-c834-482c-b3ed-81f20eeabb64'
  )
  SELECT
    patient_id,
    COUNT(*) AS nbre_doses_hpv,
    MAX(CASE WHEN rn = 1 THEN performed_on END) AS d1_hpv,
    MAX(CASE WHEN rn = 2 THEN performed_on END) AS d2_hpv,
    MAX(CASE WHEN rn = 3 THEN performed_on END) AS d3_hpv
  FROM hpv_doses
  GROUP BY patient_id
),

/* =========================
   ROR
   ========================= */
ror_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_ror,
    MIN(v.performed_on) AS premiere_ror
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '9435556b-4fce-44f7-81d6-e5d2d240860b',
    '9a08067b-3ec7-4229-a6f6-0803049ee699',
    'd50a6151-d732-4232-953d-09d95e0c6c3b'
  )
  GROUP BY v.patient_id
),

/* =========================
   Grippe
   ========================= */
grippe_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_grippe,
    MAX(v.performed_on) AS derniere_grippe
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    'd5201c83-c7fe-48a4-b34c-f38e78f39cac',
    '113c41b5-19c4-4788-811b-0fa81a18d636',
    '921a4957-762d-4ae4-ad70-bab26b28c5d2',
    '9dd17477-32e9-4078-927c-90a7bd737628',
    '7ae62689-ae48-41a1-afba-7d5a1e9f511d',
    'c39cdd50-8fec-40ef-8428-a05292f820d7'
  )
  GROUP BY v.patient_id
),

patients_enrichis AS (
  SELECT
    p.id,
    p.last_name   AS Nom,
    p.first_names AS "Prénom",
    p.gender      AS Sexe,
    p.birth_date  AS "Date de naissance",
    p.tags,
    p.created_at  AS "Création du dossier",
    p.address1 AS "Adresse",
    p.address2 AS "Complément d'adresse",
    p.city_zip_code AS "Code postal",
    p.city AS "Commune",
    p.email AS "Email",
    p.email_2 AS "Email secondaire",
    p.phone_number AS "Téléphone",
    p.phone_number_2 AS "Téléphone secondaire",

    date_diff('year', p.birth_date, current_date) AS age_ans,
	
	coalesce(d.nbre_doses_dtp, 0) AS nbre_doses_dtp,
	d.derniere_dtp,
	coalesce(d.derniere_dtp_est_booster, false) AS derniere_dtp_est_booster,

    COALESCE(c.nbre_doses_coq, 0) AS nbre_doses_coq,
    c.derniere_coq,

    COALESCE(r.nbre_doses_ror, 0) AS nbre_doses_ror,
    r.premiere_ror,

    COALESCE(hpv.nbre_doses_hpv, 0) AS nbre_doses_hpv,
    hpv.d1_hpv,
    hpv.d2_hpv,
    hpv.d3_hpv,

    COALESCE(g.nbre_doses_grippe, 0) AS nbre_doses_grippe,
    g.derniere_grippe,

    /* ===== FLAGS ===== */
	(
	  d.derniere_dtp IS NULL
	  OR d.derniere_dtp < (p.birth_date + params.min_age_dtp)
	  OR (
	    coalesce(d.nbre_doses_dtp, 0) < params.min_doses_dtp
	    AND coalesce(d.derniere_dtp_est_booster, false) = false
	  )
	) AS dtpcoq_a_faire,

    (
      c.derniere_coq IS NULL
      OR c.derniere_coq < (p.birth_date + params.min_age_coq)
    ) AS coq_a_faire,
	
    (
      COALESCE(r.nbre_doses_ror, 0) < 2
      OR (
        COALESCE(r.nbre_doses_ror, 0) < 3
        AND r.premiere_ror IS NOT NULL
        AND r.premiere_ror < (
          p.birth_date
          + params.age_ror_reference
          - params.marge_ror
        )
      )
    ) AS ror_a_faire,

    (
      COALESCE(hpv.nbre_doses_hpv, 0) < 2
    ) AS hpv_a_faire,

    (
      g.derniere_grippe IS NULL
      OR date_diff('day', g.derniere_grippe, current_date) > params.delai_grippe_jours
    ) AS grippe_a_faire,
    
    params.tags_exclus

  FROM patients p
  CROSS JOIN params
  LEFT JOIN dtpc_par_patient d   ON d.patient_id = p.id
  LEFT JOIN coq_par_patient c    ON c.patient_id = p.id
  LEFT JOIN ror_par_patient r    ON r.patient_id = p.id
  LEFT JOIN hpv_par_patient hpv  ON hpv.patient_id = p.id
  LEFT JOIN grippe_par_patient g ON g.patient_id = p.id
)

SELECT
  Nom,
  "Prénom",
  Sexe,
  "Date de naissance",

  /* Âge lisible : années + mois (calcul DuckDB) */
  (
    CAST(date_diff('month', "Date de naissance", current_date) / 12 AS INTEGER) || 'a '
    || CAST(date_diff('month', "Date de naissance", current_date) % 12 AS INTEGER) || 'm'
  ) AS "Âge ce jour",

  "Adresse",
  "Complément d'adresse",
  "Code postal",
  "Commune",
  "Email",
  "Email secondaire",
  "Téléphone",
  "Téléphone secondaire",
  "Création du dossier",
  
  nbre_doses_dtp	AS "Doses DTP",
  CASE
    WHEN derniere_dtp_est_booster THEN 'Oui'
    ELSE NULL
  END AS "dd DTP = Booster",
  
  CASE
    WHEN derniere_dtp IS NULL THEN NULL
    ELSE
      trim(
        BOTH ' ' FROM
          concat(
            CASE WHEN dtp.y > 0 THEN dtp.y || 'a ' ELSE '' END,
            CASE WHEN dtp.m > 0 THEN dtp.m || 'm ' ELSE '' END,
            dtp.d || 'j'
          )
      )
  END AS "Âge dd DTP",
  
  nbre_doses_coq AS "Doses Coq",
  CASE
    WHEN derniere_coq IS NULL THEN NULL
    ELSE
      trim(
        BOTH ' ' FROM
          concat(
            CASE WHEN coq.y > 0 THEN coq.y || 'a ' ELSE '' END,
            CASE WHEN coq.m > 0 THEN coq.m || 'm ' ELSE '' END,
            coq.d || 'j'
          )
      )
  END AS "Âge dd Coq",
 
  nbre_doses_ror AS "Doses ROR",
  nbre_doses_hpv AS "Doses HPV",
  nbre_doses_grippe AS "Doses Grippe",

  array_to_string(
    list_filter(
      list_value(
	    CASE WHEN dtpcoq_a_faire THEN 'dtP' END,
        CASE WHEN coq_a_faire    THEN 'Coq' END,
		CASE WHEN ror_a_faire    THEN 'ROR' END,
        CASE WHEN hpv_a_faire    THEN 'HPV' END,
        CASE WHEN grippe_a_faire THEN 'Grippe' END
      ),
      x -> x IS NOT NULL
    ),
    ', '
  ) AS "Vaccins à faire"

FROM patients_enrichis
LEFT JOIN LATERAL (
  SELECT
    EXTRACT(YEAR  FROM age(derniere_dtp, "Date de naissance"))::int AS y,
    EXTRACT(MONTH FROM age(derniere_dtp, "Date de naissance"))::int AS m,
    EXTRACT(DAY   FROM age(derniere_dtp, "Date de naissance"))::int AS d
) dtp ON true
LEFT JOIN LATERAL (
  SELECT
    EXTRACT(YEAR  FROM age(derniere_coq, "Date de naissance"))::int AS y,
    EXTRACT(MONTH FROM age(derniere_coq, "Date de naissance"))::int AS m,
    EXTRACT(DAY   FROM age(derniere_coq, "Date de naissance"))::int AS d
) coq ON true

WHERE age_ans BETWEEN 25 AND 26
  AND NOT EXISTS (
  SELECT 1
  FROM unnest(patients_enrichis.tags_exclus) AS t(tag)
  WHERE list_contains(
    list_transform(coalesce(patients_enrichis.tags, []), x -> lower(x)),
    lower(tag)
    )
  )

ORDER BY Nom, "Prénom";

Liste des adultes de 27 à 40 ans révolus à convoquer pour mise à jour des vaccinations contre : dtcaP, ROR, et grippe saisonnière

  • Sélection des patients : oui
  • Récupération des carnets : oui
  • Récupération des diagnostics : non

Algorithme : 

Est convoqué pour une dose  de : 

  • DTP si n'a pas reçu 3 doses (ou dernière dose est un rappel) d'un vaccin au minimum pentavalent, avec une dernière dose à partir de l'âge de 20 ans ;
  • Coqueluche si n'a pas reçu au moins 1 dose à partir de l'âge de 20 ans ;
  • ROR si n'a pas reçu 2 doses de vaccin ROR avec d1 à partir de 12 mois ou 3 doses ;
  • Grippe si n'a pas reçu 1 dose vaccinale depuis mois de 6 mois.

La dernière colonne indique les maladies pour lesquelles le patient doit être convoqué.

L'export Excel des résultats de cette requête est prêt pour une exploitation en publipostage (par voie postale, e-mail ou SMS, tous les champs nécessaires étant présents).

WITH params AS (
  SELECT
    3                      AS min_doses_dtp,
    INTERVAL '20 years'    AS min_age_dtp,
    INTERVAL '20 years'    AS min_age_coq,
    2                      AS min_doses_ror,
    INTERVAL '12 months'   AS age_ror_reference,
    INTERVAL '2 weeks'     AS marge_ror,
    183                    AS delai_grippe_jours,
    ['']                   AS tags_exclus           -- Liste des tags à exclure
),

/* =========================
   DTP
   ========================= */
dtp_par_patient AS (
  WITH last_dtp AS (
    SELECT DISTINCT ON (v.patient_id)
      v.patient_id,
      v.performed_on AS derniere_dtp,
      v.booster      AS derniere_dtp_est_booster
    FROM vaccinations v
    WHERE v.vaccine_id IN (
      '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA
      '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON
      'b9562145-be50-420f-a809-dd0e2762d142', -- VAXELIS
      '3ac78420-22e7-4ef4-90de-979a17726312', -- TETRAVAC-ACELLULAIRE
      'da117bfd-a4d3-4aec-9020-6a3cf1fa276a', -- INFANRIXQUINTA
      'c7d9d700-b481-4072-ab16-3b1aa7376298', -- INFANRIXTETRA
      '384198db-b13c-4e08-b7a1-9311809a21b9', -- BOOSTRIXTETRA
      'ef50c7de-8bcd-4525-b2a0-e1047c376d83', -- TETRACOQ
      '1a6e8b3b-dff8-421b-8892-8790e343127a', -- PENT-HIBEST
      '69f2e048-13fc-4848-8b91-39246baf2d93', -- PENTAVAC
      '7fa97d99-ff52-41b8-b267-679d7b003620', -- PENTACOQ
      '55adc664-d4a2-4154-80b2-dabd139fed28', -- REPEVAX
      'd51d40bc-3646-4dc3-8cb9-e810ce2e1029', -- DTP - Diphtérie-Tétanos-Polio, sans précision
      '768562aa-706b-4d6a-91e2-f49fe9ad3b96', -- Diphtérie-Tétanos-Polio, dose réduite, sans précision
      'd64aa9db-cb9e-4b55-997f-08a06cdcf87b', -- Vaccin hexavalent DTPCoqHibHepB, sans précision
      'fdac4922-a30a-48d0-88bd-7c16c872528f', -- Vaccin DiphTP - Diphtérie-Tétanos-Polio, sans précision
      'e3981dfe-99e5-4092-b073-5639d6761e08', -- Vaccin pentavalent DTCoqHibP - Diphtérie-Tétanos-Coqueluche-Hib-Polio, sans précision
      'c9e71f76-f164-4246-92ea-d750c128f199', -- Vaccin DiphTPCoq - Diphtérie-Tétanos-Coqueluche-Polio, sans précision
      'ffc5a77a-87e1-420d-80c9-69e0b3fb9474', -- HEXAVAC
      '55adc664-d4a2-4154-80b2-dabd139fed28'  -- REPEVAX
    )
    ORDER BY v.patient_id, v.performed_on DESC, v.id DESC
  )
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_dtp,
    MAX(v.performed_on) AS derniere_dtp,
    COALESCE(MAX(last_dtp.derniere_dtp_est_booster::int), 0)::boolean AS derniere_dtp_est_booster
  FROM vaccinations v
  LEFT JOIN last_dtp ON last_dtp.patient_id = v.patient_id
  WHERE v.vaccine_id IN (
      '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA
      '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON
      'b9562145-be50-420f-a809-dd0e2762d142', -- VAXELIS
      '3ac78420-22e7-4ef4-90de-979a17726312', -- TETRAVAC-ACELLULAIRE
      'da117bfd-a4d3-4aec-9020-6a3cf1fa276a', -- INFANRIXQUINTA
      'c7d9d700-b481-4072-ab16-3b1aa7376298', -- INFANRIXTETRA
      '384198db-b13c-4e08-b7a1-9311809a21b9', -- BOOSTRIXTETRA
      'ef50c7de-8bcd-4525-b2a0-e1047c376d83', -- TETRACOQ
      '1a6e8b3b-dff8-421b-8892-8790e343127a', -- PENT-HIBEST
      '69f2e048-13fc-4848-8b91-39246baf2d93', -- PENTAVAC
      '7fa97d99-ff52-41b8-b267-679d7b003620', -- PENTACOQ
      '55adc664-d4a2-4154-80b2-dabd139fed28', -- REPEVAX
      'd51d40bc-3646-4dc3-8cb9-e810ce2e1029', -- DTP - Diphtérie-Tétanos-Polio, sans précision
      '768562aa-706b-4d6a-91e2-f49fe9ad3b96', -- Diphtérie-Tétanos-Polio, dose réduite, sans précision
      'd64aa9db-cb9e-4b55-997f-08a06cdcf87b', -- Vaccin hexavalent DTPCoqHibHepB, sans précision
      'fdac4922-a30a-48d0-88bd-7c16c872528f', -- Vaccin DiphTP - Diphtérie-Tétanos-Polio, sans précision
      'e3981dfe-99e5-4092-b073-5639d6761e08', -- Vaccin pentavalent DTCoqHibP - Diphtérie-Tétanos-Coqueluche-Hib-Polio, sans précision
      'c9e71f76-f164-4246-92ea-d750c128f199', -- Vaccin DiphTPCoq - Diphtérie-Tétanos-Coqueluche-Polio, sans précision
      'ffc5a77a-87e1-420d-80c9-69e0b3fb9474', -- HEXAVAC
      '55adc664-d4a2-4154-80b2-dabd139fed28'  -- REPEVAX
  )
  GROUP BY v.patient_id
),

/* =========================
   Coqueluche
   ========================= */
coq_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_coq,
    MAX(v.performed_on) AS derniere_coq
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '384198db-b13c-4e08-b7a1-9311809a21b9', -- BOOSTRIXTETRA
    '55adc664-d4a2-4154-80b2-dabd139fed28'  -- REPEVAX
  )
  GROUP BY v.patient_id
),

/* =========================
   ROR
   ========================= */
ror_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_ror,
    MIN(v.performed_on) AS premiere_ror
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '9435556b-4fce-44f7-81d6-e5d2d240860b',
    '9a08067b-3ec7-4229-a6f6-0803049ee699',
    'd50a6151-d732-4232-953d-09d95e0c6c3b'
  )
  GROUP BY v.patient_id
),

/* =========================
   Grippe
   ========================= */
grippe_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_grippe,
    MAX(v.performed_on) AS derniere_grippe
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    'd5201c83-c7fe-48a4-b34c-f38e78f39cac',
    '113c41b5-19c4-4788-811b-0fa81a18d636',
    '921a4957-762d-4ae4-ad70-bab26b28c5d2',
    '9dd17477-32e9-4078-927c-90a7bd737628',
    '7ae62689-ae48-41a1-afba-7d5a1e9f511d',
    'c39cdd50-8fec-40ef-8428-a05292f820d7'
  )
  GROUP BY v.patient_id
),

patients_enrichis AS (
  SELECT
    p.id,
    p.last_name   AS Nom,
    p.first_names AS "Prénom",
    p.gender      AS Sexe,
    p.birth_date  AS "Date de naissance",
    p.tags,
    p.created_at  AS "Création du dossier",
    p.address1 AS "Adresse",
    p.address2 AS "Complément d'adresse",
    p.city_zip_code AS "Code postal",
    p.city AS "Commune",
    p.email AS "Email",
    p.email_2 AS "Email secondaire",
    p.phone_number AS "Téléphone",
    p.phone_number_2 AS "Téléphone secondaire",

    date_diff('year', p.birth_date, current_date) AS age_ans,

    coalesce(d.nbre_doses_dtp, 0) AS nbre_doses_dtp,
    d.derniere_dtp,
    coalesce(d.derniere_dtp_est_booster, false) AS derniere_dtp_est_booster,

    COALESCE(c.nbre_doses_coq, 0) AS nbre_doses_coq,
    c.derniere_coq,

    COALESCE(r.nbre_doses_ror, 0) AS nbre_doses_ror,
    r.premiere_ror,

    COALESCE(g.nbre_doses_grippe, 0) AS nbre_doses_grippe,
    g.derniere_grippe,

    /* ===== FLAGS ===== */
    (
      d.derniere_dtp IS NULL
      OR d.derniere_dtp < (p.birth_date + params.min_age_dtp)
      OR (
        coalesce(d.nbre_doses_dtp, 0) < params.min_doses_dtp
        AND coalesce(d.derniere_dtp_est_booster, false) = false
      )
    ) AS dtpcoq_a_faire,

    (
      c.derniere_coq IS NULL
      OR c.derniere_coq < (p.birth_date + params.min_age_coq)
    ) AS coq_a_faire,

    (
      COALESCE(r.nbre_doses_ror, 0) < 2
      OR (
        COALESCE(r.nbre_doses_ror, 0) < 3
        AND r.premiere_ror IS NOT NULL
        AND r.premiere_ror < (
          p.birth_date
          + params.age_ror_reference
          - params.marge_ror
        )
      )
    ) AS ror_a_faire,

    (
      g.derniere_grippe IS NULL
      OR date_diff('day', g.derniere_grippe, current_date) > params.delai_grippe_jours
    ) AS grippe_a_faire,
    
    params.tags_exclus

  FROM patients p
  CROSS JOIN params
  LEFT JOIN dtp_par_patient d    ON d.patient_id = p.id
  LEFT JOIN coq_par_patient c    ON c.patient_id = p.id
  LEFT JOIN ror_par_patient r    ON r.patient_id = p.id
  LEFT JOIN grippe_par_patient g ON g.patient_id = p.id
)

SELECT
  Nom,
  "Prénom",
  Sexe,
  "Date de naissance",

  /* Âge lisible : années + mois (calcul DuckDB) */
  (
    CAST(date_diff('month', "Date de naissance", current_date) / 12 AS INTEGER) || 'a '
    || CAST(date_diff('month', "Date de naissance", current_date) % 12 AS INTEGER) || 'm'
  ) AS "Âge ce jour",

  "Adresse",
  "Complément d'adresse",
  "Code postal",
  "Commune",
  "Email",
  "Email secondaire",
  "Téléphone",
  "Téléphone secondaire",
  "Création du dossier",

  nbre_doses_dtp AS "Doses DTP",
  CASE
    WHEN derniere_dtp_est_booster THEN 'Oui'
    ELSE NULL
  END AS "dd DTP = Booster",

  CASE
    WHEN derniere_dtp IS NULL THEN NULL
    ELSE
      trim(
        BOTH ' ' FROM
          concat(
            CASE WHEN dtp.y > 0 THEN dtp.y || 'a ' ELSE '' END,
            CASE WHEN dtp.m > 0 THEN dtp.m || 'm ' ELSE '' END,
            dtp.d || 'j'
          )
      )
  END AS "Âge dd DTP",

  nbre_doses_coq AS "Doses Coq",
  CASE
    WHEN derniere_coq IS NULL THEN NULL
    ELSE
      trim(
        BOTH ' ' FROM
          concat(
            CASE WHEN coq.y > 0 THEN coq.y || 'a ' ELSE '' END,
            CASE WHEN coq.m > 0 THEN coq.m || 'm ' ELSE '' END,
            coq.d || 'j'
          )
      )
  END AS "Âge dd Coq",

  nbre_doses_ror AS "Doses ROR",
  nbre_doses_grippe AS "Doses Grippe",

  array_to_string(
    list_filter(
      list_value(
        CASE WHEN dtpcoq_a_faire THEN 'dtP' END,
        CASE WHEN coq_a_faire    THEN 'Coq' END,
        CASE WHEN ror_a_faire    THEN 'ROR' END,
        CASE WHEN grippe_a_faire THEN 'Grippe' END
      ),
      x -> x IS NOT NULL
    ),
    ', '
  ) AS "Vaccins à faire"

FROM patients_enrichis
LEFT JOIN LATERAL (
  SELECT
    EXTRACT(YEAR  FROM age(derniere_dtp, "Date de naissance"))::int AS y,
    EXTRACT(MONTH FROM age(derniere_dtp, "Date de naissance"))::int AS m,
    EXTRACT(DAY   FROM age(derniere_dtp, "Date de naissance"))::int AS d
) dtp ON true
LEFT JOIN LATERAL (
  SELECT
    EXTRACT(YEAR  FROM age(derniere_coq, "Date de naissance"))::int AS y,
    EXTRACT(MONTH FROM age(derniere_coq, "Date de naissance"))::int AS m,
    EXTRACT(DAY   FROM age(derniere_coq, "Date de naissance"))::int AS d
) coq ON true

WHERE age_ans BETWEEN 27 AND 41
  AND NOT EXISTS (
  SELECT 1
  FROM unnest(patients_enrichis.tags_exclus) AS t(tag)
  WHERE list_contains(
    list_transform(coalesce(patients_enrichis.tags, []), x -> lower(x)),
    lower(tag)
    )
  )
  
ORDER BY Nom, "Prénom";

Liste des adultes de 41 à 64 ans révolus à convoquer pour mise à jour des vaccinations contre dtcaP

  • Sélection des patients : oui
  • Récupération des carnets : oui
  • Récupération des diagnostics : non

Algorithme : 

Est convoqué pour une dose  de DTP si n'a pas reçu au moins 3 doses (ou dernière dose est un rappel) d'un vaccin au minimum avec valences diphtérie-tétanos-polio, avec une dernière dose à partir de l'âge de 40 ans.

WITH params AS (
  SELECT
    3     AS min_doses_dtp,
    ['']  AS tags_exclus      -- Liste des tags à exclure
),

/* =========================
   DTP
   ========================= */
dtp_par_patient AS (
  WITH last_dtp AS (
    SELECT DISTINCT ON (v.patient_id)
      v.patient_id,
      v.performed_on AS derniere_dtp,
      v.booster      AS derniere_dtp_est_booster
    FROM vaccinations v
    WHERE v.vaccine_id IN (
      '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA (DTPCaHibHepB)
      '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON (DTPCaHibHepB)
      'ffc5a77a-87e1-420d-80c9-69e0b3fb9474', -- HEXAVAC (DTPCaHibHepB)
      'b9562145-be50-420f-a809-dd0e2762d142', -- VAXELIS (DTPCaHibHepB)
      'da117bfd-a4d3-4aec-9020-6a3cf1fa276a', -- INFANRIXQUINTA (DTCaPHib)
      '69f2e048-13fc-4848-8b91-39246baf2d93', -- PENTAVAC (DTCaPHib)
      '3ac78420-22e7-4ef4-90de-979a17726312', -- TETRAVAC-ACELLULAIRE (DTCaP)
      'c7d9d700-b481-4072-ab16-3b1aa7376298', -- INFANRIXTETRA (DTCaP)
      '384198db-b13c-4e08-b7a1-9311809a21b9', -- BOOSTRIXTETRA (dTcaP)
      '55adc664-d4a2-4154-80b2-dabd139fed28', -- REPEVAX (dTcaP)
      'ef50c7de-8bcd-4525-b2a0-e1047c376d83', -- TETRACOQ (DTCeP)
      '1a6e8b3b-dff8-421b-8892-8790e343127a', -- PENT-HIBEST (DTcePHib)
      '7fa97d99-ff52-41b8-b267-679d7b003620', -- PENTACOQ (DTcePHib)
      '3fddc43f-7d06-4186-8160-b42f230af0a7', -- Vaccin D.T.P. Pasteur (DTP)
      'd51d40bc-3646-4dc3-8cb9-e810ce2e1029', -- DTP sans précision
      'f104fe51-74df-4b67-a85e-8acd32ad4a6c', -- REVAXIS (dTP)
      '768562aa-706b-4d6a-91e2-f49fe9ad3b96', -- Vaccin dTP sans précision
      'd64aa9db-cb9e-4b55-997f-08a06cdcf87b', -- Vaccin hexavalent sans précision
      'fdac4922-a30a-48d0-88bd-7c16c872528f', -- Vaccin DiphTP sans précision
      'e3981dfe-99e5-4092-b073-5639d6761e08', -- Vaccin pentavalent sans précision
      'c9e71f76-f164-4246-92ea-d750c128f199'  -- Vaccin DiphTPCoq sans précision
    )
    ORDER BY v.patient_id, v.performed_on DESC, v.id DESC
  )
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_dtp,
    MAX(v.performed_on) AS derniere_dtp,
    COALESCE(MAX(last_dtp.derniere_dtp_est_booster::int), 0)::boolean AS derniere_dtp_est_booster
  FROM vaccinations v
  LEFT JOIN last_dtp ON last_dtp.patient_id = v.patient_id
  WHERE v.vaccine_id IN (
      '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA (DTPCaHibHepB)
      '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON (DTPCaHibHepB)
      'ffc5a77a-87e1-420d-80c9-69e0b3fb9474', -- HEXAVAC (DTPCaHibHepB)
      'b9562145-be50-420f-a809-dd0e2762d142', -- VAXELIS (DTPCaHibHepB)
      'da117bfd-a4d3-4aec-9020-6a3cf1fa276a', -- INFANRIXQUINTA (DTCaPHib)
      '69f2e048-13fc-4848-8b91-39246baf2d93', -- PENTAVAC (DTCaPHib)
      '3ac78420-22e7-4ef4-90de-979a17726312', -- TETRAVAC-ACELLULAIRE (DTCaP)
      'c7d9d700-b481-4072-ab16-3b1aa7376298', -- INFANRIXTETRA (DTCaP)
      '384198db-b13c-4e08-b7a1-9311809a21b9', -- BOOSTRIXTETRA (dTcaP)
      '55adc664-d4a2-4154-80b2-dabd139fed28', -- REPEVAX (dTcaP)
      'ef50c7de-8bcd-4525-b2a0-e1047c376d83', -- TETRACOQ (DTCeP)
      '1a6e8b3b-dff8-421b-8892-8790e343127a', -- PENT-HIBEST (DTcePHib)
      '7fa97d99-ff52-41b8-b267-679d7b003620', -- PENTACOQ (DTcePHib)
      '3fddc43f-7d06-4186-8160-b42f230af0a7', -- Vaccin D.T.P. Pasteur (DTP)
      'd51d40bc-3646-4dc3-8cb9-e810ce2e1029', -- DTP sans précision
      'f104fe51-74df-4b67-a85e-8acd32ad4a6c', -- REVAXIS (dTP)
      '768562aa-706b-4d6a-91e2-f49fe9ad3b96', -- Vaccin dTP sans précision
      'd64aa9db-cb9e-4b55-997f-08a06cdcf87b', -- Vaccin hexavalent sans précision
      'fdac4922-a30a-48d0-88bd-7c16c872528f', -- Vaccin DiphTP sans précision
      'e3981dfe-99e5-4092-b073-5639d6761e08', -- Vaccin pentavalent sans précision
      'c9e71f76-f164-4246-92ea-d750c128f199'  -- Vaccin DiphTPCoq sans précision
  )
  GROUP BY v.patient_id
),

patients_base AS (
  SELECT
    p.id,
    p.last_name   AS Nom,
    p.first_names AS "Prénom",
    p.gender      AS Sexe,
    p.birth_date  AS "Date de naissance",
    p.tags,
    p.created_at  AS "Création du dossier",
    p.address1 AS "Adresse",
    p.address2 AS "Complément d'adresse",
    p.city_zip_code AS "Code postal",
    p.city AS "Commune",
    p.email AS "Email",
    p.email_2 AS "Email secondaire",
    p.phone_number AS "Téléphone",
    p.phone_number_2 AS "Téléphone secondaire",

    date_diff('year', p.birth_date, current_date) AS age_ans,

    COALESCE(d.nbre_doses_dtp, 0) AS nbre_doses_dtp,
    d.derniere_dtp,
    COALESCE(d.derniere_dtp_est_booster, false) AS derniere_dtp_est_booster
  FROM patients p
  LEFT JOIN dtp_par_patient d ON d.patient_id = p.id
),

patients_enrichis AS (
  SELECT
    pb.*,
    (
      pb.derniere_dtp IS NULL

      OR (
        pb.age_ans BETWEEN 40 AND 64
        AND pb.derniere_dtp < (pb."Date de naissance" + INTERVAL '40 years')
      )

      OR (
        pb.nbre_doses_dtp < (SELECT min_doses_dtp FROM params)
        AND pb.derniere_dtp_est_booster = false
      )
    ) AS dtp_a_faire,
    params.tags_exclus
  FROM patients_base pb
  CROSS JOIN params
)

SELECT
  Nom,
  "Prénom",
  Sexe,
  "Date de naissance",
  tags,

  (
    CAST(date_diff('month', "Date de naissance", current_date) / 12 AS INTEGER) || 'a '
    || CAST(date_diff('month', "Date de naissance", current_date) % 12 AS INTEGER) || 'm'
  ) AS "Âge ce jour",

  "Adresse",
  "Complément d'adresse",
  "Code postal",
  "Commune",
  "Email",
  "Email secondaire",
  "Téléphone",
  "Téléphone secondaire",
  "Création du dossier",

  nbre_doses_dtp AS "Doses DTP",
  CASE
    WHEN derniere_dtp_est_booster THEN 'Oui'
    ELSE NULL
  END AS "dd DTP = Booster",

  CASE
    WHEN derniere_dtp IS NULL THEN NULL
    ELSE trim(
      BOTH ' ' FROM concat(
        CASE WHEN dtp.y > 0 THEN dtp.y || 'a ' ELSE '' END,
        CASE WHEN dtp.m > 0 THEN dtp.m || 'm ' ELSE '' END,
        dtp.d || 'j'
      )
    )
  END AS "Âge dd DTP",

  array_to_string(
    list_filter(
      list_value(
        CASE WHEN dtp_a_faire THEN 'dtP' END
      ),
      x -> x IS NOT NULL
    ),
    ', '
  ) AS "Vaccins à faire"

FROM patients_enrichis
LEFT JOIN LATERAL (
  SELECT
    EXTRACT(YEAR  FROM age(derniere_dtp, "Date de naissance"))::int AS y,
    EXTRACT(MONTH FROM age(derniere_dtp, "Date de naissance"))::int AS m,
    EXTRACT(DAY   FROM age(derniere_dtp, "Date de naissance"))::int AS d
) dtp ON true

WHERE age_ans BETWEEN 41 AND 64
  AND NOT EXISTS (
  SELECT 1
  FROM unnest(patients_enrichis.tags_exclus) AS t(tag)
  WHERE list_contains(
    list_transform(coalesce(patients_enrichis.tags, []), x -> lower(x)),
    lower(tag)
    )
  )
  
ORDER BY Nom, "Prénom";

Liste des adultes de 65 à 74 ans révolus à convoquer pour mise à jour des vaccinations contre : dtcaP, pneumocoque, zona, covid 19 et grippe saisonnière

  • Sélection des patients : oui
  • Récupération des carnets : oui
  • Récupération des diagnostics : non

Algorithme : 

Est convoqué pour une dose  de : 

  • DTP si n'a pas reçu au moins 3 doses (ou dernière dose est un rappel) d'un vaccin au minimum avec valences diphtérie-tétanos-polio, avec une dernière dose à partir de l'âge de 60 ans  ;
  • Pneumocoque (voir détail de l'algorithme) ;
  • Zona si n'a pas reçu 2 doses ;
  • covid 19 si n'a pas reçu 1 dose vaccinale depuis moins de 6 mois ;
  • Grippe si n'a pas reçu 1 dose vaccinale depuis moins de 6 mois.

Algorithme pneumocoque :

A jour si :

 ≥ 1 dose de 20-valent
  OU
≥ 1 dose de 21-valent
  OU
≥ 1 dose de 13-valent ET ≥ 1 dose de 23-valent ET dd = 23-valent ET dd depuis moins de 5 ans
  OU
≥ 1 dose de 15-valent ET ≥ 1 dose de 23-valent ET dd = 23-valent ET dd depuis moins de 5 ans
  OU
uniquement 7-valent ET dd depuis moins de 1 an
  OU
uniquement 13-valent ET dd depuis moins de 1 an
  OU
uniquement 15-valent ET dd depuis moins de 1 an
  OU
uniquement 23-valent ET dd depuis moins de 1 an

Si aucune de ces conditions n'est vérifiée : à faire.

La dernière colonne indique les maladies pour lesquelles le patient doit être convoqué.

L'export Excel des résultats de cette requête est prêt pour une exploitation en publipostage (par voie postale, e-mail ou SMS, tous les champs nécessaires étant présents).

WITH params AS (
  SELECT
    3    AS min_doses_dtp,
    183  AS delai_grippe_jours,
    183  AS delai_covid_jours,
    365  AS delai_pneumo_1an_jours,
    1825 AS delai_pneumo_5ans_jours,
    [''] AS tags_exclus                 -- Liste des tags à exclure
),

/* =========================
   DTP
   ========================= */
dtp_par_patient AS (
  WITH last_dtp AS (
    SELECT DISTINCT ON (v.patient_id)
      v.patient_id,
      v.performed_on AS derniere_dtp,
      v.booster      AS derniere_dtp_est_booster
    FROM vaccinations v
    WHERE v.vaccine_id IN (
      '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA (DTPCaHibHepB)
      '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON (DTPCaHibHepB)
      'ffc5a77a-87e1-420d-80c9-69e0b3fb9474', -- HEXAVAC (DTPCaHibHepB)
      'b9562145-be50-420f-a809-dd0e2762d142', -- VAXELIS (DTPCaHibHepB)
      'da117bfd-a4d3-4aec-9020-6a3cf1fa276a', -- INFANRIXQUINTA (DTCaPHib)
      '69f2e048-13fc-4848-8b91-39246baf2d93', -- PENTAVAC (DTCaPHib)
      '3ac78420-22e7-4ef4-90de-979a17726312', -- TETRAVAC-ACELLULAIRE (DTCaP)
      'c7d9d700-b481-4072-ab16-3b1aa7376298', -- INFANRIXTETRA (DTCaP)
      '384198db-b13c-4e08-b7a1-9311809a21b9', -- BOOSTRIXTETRA (dTcaP)
      '55adc664-d4a2-4154-80b2-dabd139fed28', -- REPEVAX (dTcaP)
      'ef50c7de-8bcd-4525-b2a0-e1047c376d83', -- TETRACOQ (DTCeP)
      '1a6e8b3b-dff8-421b-8892-8790e343127a', -- PENT-HIBEST (DTcePHib)
      '7fa97d99-ff52-41b8-b267-679d7b003620', -- PENTACOQ (DTcePHib)
      '3fddc43f-7d06-4186-8160-b42f230af0a7', -- Vaccin D.T.P. Pasteur (DTP)
      'd51d40bc-3646-4dc3-8cb9-e810ce2e1029', -- DTP - Diphtérie-Tétanos-Polio, sans précision (DTP)
      'f104fe51-74df-4b67-a85e-8acd32ad4a6c', -- REVAXIS (dTP)
      '768562aa-706b-4d6a-91e2-f49fe9ad3b96', -- Vaccin dTP - Diphtérie-Tétanos-Polio, dose réduite, sans précision (dTP)
      'd64aa9db-cb9e-4b55-997f-08a06cdcf87b', -- Vaccin hexavalent DTPCoqHibHepB, sans précision (DTPCoqHibHepB)
      'fdac4922-a30a-48d0-88bd-7c16c872528f', -- Vaccin DiphTP - Diphtérie-Tétanos-Polio, sans précision
      'e3981dfe-99e5-4092-b073-5639d6761e08', -- Vaccin pentavalent DTCoqHibP - Diphtérie-Tétanos-Coqueluche-Hib-Polio, sans précision
      'c9e71f76-f164-4246-92ea-d750c128f199'  -- Vaccin DiphTPCoq - Diphtérie-Tétanos-Coqueluche-Polio, sans précision

    )
    ORDER BY v.patient_id, v.performed_on DESC, v.id DESC
  )
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_dtp,
    MAX(v.performed_on) AS derniere_dtp,
    COALESCE(MAX(last_dtp.derniere_dtp_est_booster::int), 0)::boolean AS derniere_dtp_est_booster
  FROM vaccinations v
  LEFT JOIN last_dtp ON last_dtp.patient_id = v.patient_id
  WHERE v.vaccine_id IN (
      '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA (DTPCaHibHepB)
      '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON (DTPCaHibHepB)
      'ffc5a77a-87e1-420d-80c9-69e0b3fb9474', -- HEXAVAC (DTPCaHibHepB)
      'b9562145-be50-420f-a809-dd0e2762d142', -- VAXELIS (DTPCaHibHepB)
      'da117bfd-a4d3-4aec-9020-6a3cf1fa276a', -- INFANRIXQUINTA (DTCaPHib)
      '69f2e048-13fc-4848-8b91-39246baf2d93', -- PENTAVAC (DTCaPHib)
      '3ac78420-22e7-4ef4-90de-979a17726312', -- TETRAVAC-ACELLULAIRE (DTCaP)
      'c7d9d700-b481-4072-ab16-3b1aa7376298', -- INFANRIXTETRA (DTCaP)
      '384198db-b13c-4e08-b7a1-9311809a21b9', -- BOOSTRIXTETRA (dTcaP)
      '55adc664-d4a2-4154-80b2-dabd139fed28', -- REPEVAX (dTcaP)
      'ef50c7de-8bcd-4525-b2a0-e1047c376d83', -- TETRACOQ (DTCeP)
      '1a6e8b3b-dff8-421b-8892-8790e343127a', -- PENT-HIBEST (DTcePHib)
      '7fa97d99-ff52-41b8-b267-679d7b003620', -- PENTACOQ (DTcePHib)
      '3fddc43f-7d06-4186-8160-b42f230af0a7', -- Vaccin D.T.P. Pasteur (DTP)
      'd51d40bc-3646-4dc3-8cb9-e810ce2e1029', -- DTP - Diphtérie-Tétanos-Polio, sans précision (DTP)
      'f104fe51-74df-4b67-a85e-8acd32ad4a6c', -- REVAXIS (dTP)
      '768562aa-706b-4d6a-91e2-f49fe9ad3b96', -- Vaccin dTP - Diphtérie-Tétanos-Polio, dose réduite, sans précision (dTP)
      'd64aa9db-cb9e-4b55-997f-08a06cdcf87b', -- Vaccin hexavalent DTPCoqHibHepB, sans précision (DTPCoqHibHepB)
      'fdac4922-a30a-48d0-88bd-7c16c872528f', -- Vaccin DiphTP - Diphtérie-Tétanos-Polio, sans précision
      'e3981dfe-99e5-4092-b073-5639d6761e08', -- Vaccin pentavalent DTCoqHibP - Diphtérie-Tétanos-Coqueluche-Hib-Polio, sans précision
      'c9e71f76-f164-4246-92ea-d750c128f199'  -- Vaccin DiphTPCoq - Diphtérie-Tétanos-Coqueluche-Polio, sans précision

  )
  GROUP BY v.patient_id
),

/* =========================
   Grippe
   ========================= */
grippe_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_grippe,
    MAX(v.performed_on) AS derniere_grippe
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    'd5201c83-c7fe-48a4-b34c-f38e78f39cac', -- FLUCELVAX Trivalent
    '113c41b5-19c4-4788-811b-0fa81a18d636', -- INFLUVAC Trivalent
    '921a4957-762d-4ae4-ad70-bab26b28c5d2', -- VAXIGRIP Trivalent
    '9dd17477-32e9-4078-927c-90a7bd737628', -- VAXIGRIP ENFANTS Trivalent
    '7ae62689-ae48-41a1-afba-7d5a1e9f511d', -- EFLUELDA Trivalent
    'c39cdd50-8fec-40ef-8428-a05292f820d7'  -- FLUAD Trivalent
  )
  GROUP BY v.patient_id
),

/* =========================
   Covid 19
   ========================= */
covid_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_covid,
    MAX(v.performed_on) AS derniere_covid
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '5381126d-145a-49ee-bd87-c860a822fc5a', -- COMIRNATY KP.2 30 µg
    '97d043b8-a5ab-418a-bb41-326c8f5efd46'  -- COMIRNATY LP.8.1 30 µg
  )
  GROUP BY v.patient_id
),

/* =========================
   Pneumo (règles "à faire")
   ========================= */
/* =========================
   Pneumo (règles "à faire" + ADD/DD)
   ========================= */
pneumo_par_patient AS (
  WITH pneumo_ranked AS (
    SELECT
      v.patient_id,
      v.performed_on,
      v.vaccine_id,
      row_number() OVER (
        PARTITION BY v.patient_id
        ORDER BY v.performed_on DESC, v.id DESC
      ) AS rn
    FROM vaccinations v
    WHERE v.vaccine_id IN (
      'b4248431-6211-43af-bf20-09b32daf38bc', -- PREVENAR-7 (PCV7)
      '5312b079-3245-49fe-9193-4658c4c5d1d0', -- PREVENAR 13 (PCV13)
      '5e6dd99c-ef7c-4bbd-8611-a3be793d8456', -- VAXNEUVANCE (PCV15)
      '197e5bd5-e048-40fb-8d8e-bcde2f04cb3e', -- PREVENAR 20 (PCV20)
      '64adbea9-8351-4f3d-995c-1b78a79abdfd', -- CAPVAXIVE (PCV21)
      'c3ae4d8f-73f7-4601-b4c6-972ba0113aa1', -- PNEUMOVAX (PPS23)
      'd9ff5075-be06-4baf-bdb0-3956f19b48b7'  -- PNEUMO 23 (PPS23)
    )
  )
  SELECT
    patient_id,

    COUNT(*) AS nbre_doses_pneumo,

    /* DD = dernière dose */
    MAX(performed_on) FILTER (WHERE rn = 1) AS pneumo_dd_date,
    MAX(vaccine_id)   FILTER (WHERE rn = 1) AS pneumo_dd_vaccine_id,

    /* ADD = dose avant dernière */
    MAX(performed_on) FILTER (WHERE rn = 2) AS pneumo_add_date,
    MAX(vaccine_id)   FILTER (WHERE rn = 2) AS pneumo_add_vaccine_id,

    /* Pour compat : "derniere_pneumo" = DD */
    MAX(performed_on) FILTER (WHERE rn = 1) AS derniere_pneumo,

    /* Compteurs par valence */
    SUM(CASE WHEN vaccine_id = 'b4248431-6211-43af-bf20-09b32daf38bc' THEN 1 ELSE 0 END) AS nbre_pcv7,
    SUM(CASE WHEN vaccine_id = '5312b079-3245-49fe-9193-4658c4c5d1d0' THEN 1 ELSE 0 END) AS nbre_pcv13,
    SUM(CASE WHEN vaccine_id = '5e6dd99c-ef7c-4bbd-8611-a3be793d8456' THEN 1 ELSE 0 END) AS nbre_pcv15,
    SUM(CASE WHEN vaccine_id = '197e5bd5-e048-40fb-8d8e-bcde2f04cb3e' THEN 1 ELSE 0 END) AS nbre_pcv20,
    SUM(CASE WHEN vaccine_id = '64adbea9-8351-4f3d-995c-1b78a79abdfd' THEN 1 ELSE 0 END) AS nbre_pcv21,
    SUM(CASE WHEN vaccine_id IN (
        'c3ae4d8f-73f7-4601-b4c6-972ba0113aa1',
        'd9ff5075-be06-4baf-bdb0-3956f19b48b7'
      ) THEN 1 ELSE 0 END) AS nbre_pps23,

    /* Dernière dose = 23-valent ? */
    (
      MAX(
        CASE
          WHEN vaccine_id IN (
            'c3ae4d8f-73f7-4601-b4c6-972ba0113aa1',
            'd9ff5075-be06-4baf-bdb0-3956f19b48b7'
          ) AND rn = 1
          THEN 1 ELSE 0
        END
      ) = 1
    ) AS derniere_pneumo_est_23,

    /* Valence DD */
    CASE MAX(vaccine_id) FILTER (WHERE rn = 1)
      WHEN 'b4248431-6211-43af-bf20-09b32daf38bc' THEN '7-valent'
      WHEN '5312b079-3245-49fe-9193-4658c4c5d1d0' THEN '13-valent'
      WHEN '5e6dd99c-ef7c-4bbd-8611-a3be793d8456' THEN '15-valent'
      WHEN '197e5bd5-e048-40fb-8d8e-bcde2f04cb3e' THEN '20-valent'
      WHEN '64adbea9-8351-4f3d-995c-1b78a79abdfd' THEN '21-valent'
      WHEN 'c3ae4d8f-73f7-4601-b4c6-972ba0113aa1' THEN '23-valent NC'
      WHEN 'd9ff5075-be06-4baf-bdb0-3956f19b48b7' THEN '23-valent NC'
      ELSE NULL
    END AS pneumo_dd_valence,

    /* Valence ADD */
    CASE MAX(vaccine_id) FILTER (WHERE rn = 2)
      WHEN 'b4248431-6211-43af-bf20-09b32daf38bc' THEN '7-valent'
      WHEN '5312b079-3245-49fe-9193-4658c4c5d1d0' THEN '13-valent'
      WHEN '5e6dd99c-ef7c-4bbd-8611-a3be793d8456' THEN '15-valent'
      WHEN '197e5bd5-e048-40fb-8d8e-bcde2f04cb3e' THEN '20-valent'
      WHEN '64adbea9-8351-4f3d-995c-1b78a79abdfd' THEN '21-valent'
      WHEN 'c3ae4d8f-73f7-4601-b4c6-972ba0113aa1' THEN '23-valent NC'
      WHEN 'd9ff5075-be06-4baf-bdb0-3956f19b48b7' THEN '23-valent NC'
      ELSE NULL
    END AS pneumo_add_valence

  FROM pneumo_ranked
  GROUP BY patient_id
),	

/* =========================
   Zona
   ========================= */
zona_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_zona,
    MAX(v.performed_on) AS derniere_zona
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    'bd67a8e0-356c-497f-9f9d-6aeef99e92ba'  -- SHINGRIX
  )
  GROUP BY v.patient_id
),

patients_enrichis AS (
  SELECT
    p.id,
    p.last_name   AS Nom,
    p.first_names AS "Prénom",
    p.gender      AS Sexe,
    p.birth_date  AS "Date de naissance",
    p.tags,
    p.created_at  AS "Création du dossier",
    p.address1 AS "Adresse",
    p.address2 AS "Complément d'adresse",
    p.city_zip_code AS "Code postal",
    p.city AS "Commune",
    p.email AS "Email",
    p.email_2 AS "Email secondaire",
    p.phone_number AS "Téléphone",
    p.phone_number_2 AS "Téléphone secondaire",

    date_diff('year', p.birth_date, current_date) AS age_ans,

    coalesce(d.nbre_doses_dtp, 0) AS nbre_doses_dtp,
    d.derniere_dtp,
    coalesce(d.derniere_dtp_est_booster, false) AS derniere_dtp_est_booster,

    COALESCE(g.nbre_doses_grippe, 0) AS nbre_doses_grippe,
    g.derniere_grippe,

    COALESCE(cv.nbre_doses_covid, 0) AS nbre_doses_covid,
    cv.derniere_covid,

    /* pneumo + détails nécessaires aux règles */
    COALESCE(pn.nbre_doses_pneumo, 0) AS nbre_doses_pneumo,
    pn.derniere_pneumo,
    pn.pneumo_add_valence,
    pn.pneumo_dd_valence,
    pn.pneumo_add_date,
    pn.pneumo_dd_date,
    COALESCE(pn.nbre_pcv7, 0)  AS nbre_pcv7,
    COALESCE(pn.nbre_pcv13, 0) AS nbre_pcv13,
    COALESCE(pn.nbre_pcv15, 0) AS nbre_pcv15,
    COALESCE(pn.nbre_pcv20, 0) AS nbre_pcv20,
    COALESCE(pn.nbre_pcv21, 0) AS nbre_pcv21,
    COALESCE(pn.nbre_pps23, 0) AS nbre_pps23,
    COALESCE(pn.derniere_pneumo_est_23, false) AS derniere_pneumo_est_23,

    COALESCE(z.nbre_doses_zona, 0) AS nbre_doses_zona,
    z.derniere_zona,

    /* ===== FLAGS ===== */
    (
      d.derniere_dtp IS NULL

      /* règle 65–74 ans : si dd avant 60 ans -> à faire */
      OR (
        age_ans BETWEEN 65 AND 74
        AND d.derniere_dtp < (p.birth_date + INTERVAL '60 years')
      )

      /* reste inchangé */
      OR (
        COALESCE(d.nbre_doses_dtp, 0) < params.min_doses_dtp
        AND COALESCE(d.derniere_dtp_est_booster, false) = false
      )
    ) AS dtp_a_faire,

    (
      g.derniere_grippe IS NULL
      OR date_diff('day', g.derniere_grippe, current_date) > params.delai_grippe_jours
    ) AS grippe_a_faire,

    (
      cv.derniere_covid IS NULL
      OR date_diff('day', cv.derniere_covid, current_date) > params.delai_covid_jours
    ) AS covid_a_faire,

    /* =========================
       Pneumo : A FAIRE (inverse de "à jour")
       ========================= */
    (
      NOT (
        /* A JOUR SI : ≥1 dose 20-valent */
        nbre_pcv20 >= 1

        /* OU : ≥1 dose 21-valent */
        OR nbre_pcv21 >= 1

        /* OU : PCV13 + PPS23, dernière = PPS23, et dd < 5 ans */
        OR (
          nbre_pcv13 >= 1
          AND nbre_pps23 >= 1
          AND derniere_pneumo_est_23 = true
          AND derniere_pneumo IS NOT NULL
          AND date_diff('day', derniere_pneumo, current_date) <= params.delai_pneumo_5ans_jours
        )

        /* OU : PCV15 + PPS23, dernière = PPS23, et dd < 5 ans */
        OR (
          nbre_pcv15 >= 1
          AND nbre_pps23 >= 1
          AND derniere_pneumo_est_23 = true
          AND derniere_pneumo IS NOT NULL
          AND date_diff('day', derniere_pneumo, current_date) <= params.delai_pneumo_5ans_jours
        )

        /* OU : uniquement PCV7, dd < 1 an */
        OR (
          nbre_pcv7 >= 1
          AND nbre_doses_pneumo = nbre_pcv7
          AND derniere_pneumo IS NOT NULL
          AND date_diff('day', derniere_pneumo, current_date) <= params.delai_pneumo_1an_jours
        )

        /* OU : uniquement PCV13, dd < 1 an */
        OR (
          nbre_pcv13 >= 1
          AND nbre_doses_pneumo = nbre_pcv13
          AND derniere_pneumo IS NOT NULL
          AND date_diff('day', derniere_pneumo, current_date) <= params.delai_pneumo_1an_jours
        )

        /* OU : uniquement PCV15, dd < 1 an */
        OR (
          nbre_pcv15 >= 1
          AND nbre_doses_pneumo = nbre_pcv15
          AND derniere_pneumo IS NOT NULL
          AND date_diff('day', derniere_pneumo, current_date) <= params.delai_pneumo_1an_jours
        )

        /* OU : uniquement PPS23, dd < 1 an */
        OR (
          nbre_pps23 >= 1
          AND nbre_doses_pneumo = nbre_pps23
          AND derniere_pneumo IS NOT NULL
          AND date_diff('day', derniere_pneumo, current_date) <= params.delai_pneumo_1an_jours
        )
      )
    ) AS pneumo_a_faire,

    (
      COALESCE(z.nbre_doses_zona, 0) < 2
    ) AS zona_a_faire,
    
    params.tags_exclus

  FROM patients p
  CROSS JOIN params
  LEFT JOIN dtp_par_patient d      ON d.patient_id = p.id
  LEFT JOIN grippe_par_patient g   ON g.patient_id = p.id
  LEFT JOIN covid_par_patient cv   ON cv.patient_id = p.id
  LEFT JOIN pneumo_par_patient pn  ON pn.patient_id = p.id
  LEFT JOIN zona_par_patient z     ON z.patient_id = p.id
)

SELECT
  Nom,
  "Prénom",
  Sexe,
  "Date de naissance",

  /* Âge lisible : années + mois (calcul DuckDB) */
  (
    CAST(date_diff('month', "Date de naissance", current_date) / 12 AS INTEGER) || 'a '
    || CAST(date_diff('month', "Date de naissance", current_date) % 12 AS INTEGER) || 'm'
  ) AS "Âge ce jour",

  "Adresse",
  "Complément d'adresse",
  "Code postal",
  "Commune",
  "Email",
  "Email secondaire",
  "Téléphone",
  "Téléphone secondaire",
  "Création du dossier",

  nbre_doses_dtp AS "Doses DTP",
  CASE
    WHEN derniere_dtp_est_booster THEN 'Oui'
    ELSE NULL
  END AS "dd DTP = Booster",

  CASE
    WHEN derniere_dtp IS NULL THEN NULL
    ELSE
      trim(
        BOTH ' ' FROM
          concat(
            CASE WHEN dtp.y > 0 THEN dtp.y || 'a ' ELSE '' END,
            CASE WHEN dtp.m > 0 THEN dtp.m || 'm ' ELSE '' END,
            dtp.d || 'j'
          )
      )
  END AS "Âge dd DTP",

  nbre_doses_grippe AS "Doses Grippe",
  nbre_doses_covid  AS "Doses Covid",
  
  nbre_doses_pneumo AS "Doses Pneumo",
  CASE
    WHEN nbre_doses_pneumo >= 2 THEN pneumo_add_valence
    ELSE NULL
  END AS "Valence ADD",

  CASE
    WHEN nbre_doses_pneumo >= 1 THEN pneumo_dd_valence
    ELSE NULL
  END AS "Valence DD",

  CASE
    WHEN nbre_doses_pneumo >= 2
         AND pneumo_add_date IS NOT NULL
         AND pneumo_dd_date IS NOT NULL
    THEN
      trim(
        BOTH ' ' FROM
          concat(
            CASE WHEN pned.y > 0 THEN pned.y || 'a ' ELSE '' END,
            CASE WHEN pned.m > 0 THEN pned.m || 'm ' ELSE '' END,
            pned.d || 'j'
          )
      )
    ELSE NULL
  END AS "Écart ADD-DD",
  
  nbre_doses_zona   AS "Doses Zona",

  array_to_string(
    list_filter(
      list_value(
        CASE WHEN dtp_a_faire    THEN 'dtP' END,
        CASE WHEN grippe_a_faire THEN 'Grippe' END,
        CASE WHEN covid_a_faire  THEN 'Covid' END,
        CASE WHEN pneumo_a_faire THEN 'Pneumo' END,
        CASE WHEN zona_a_faire   THEN 'Zona' END
      ),
      x -> x IS NOT NULL
    ),
    ', '
  ) AS "Vaccins à faire"

FROM patients_enrichis
LEFT JOIN LATERAL (
  SELECT
    EXTRACT(YEAR  FROM age(derniere_dtp, "Date de naissance"))::int AS y,
    EXTRACT(MONTH FROM age(derniere_dtp, "Date de naissance"))::int AS m,
    EXTRACT(DAY   FROM age(derniere_dtp, "Date de naissance"))::int AS d
) dtp ON true
LEFT JOIN LATERAL (
  SELECT
    EXTRACT(YEAR  FROM age(pneumo_dd_date, pneumo_add_date))::int AS y,
    EXTRACT(MONTH FROM age(pneumo_dd_date, pneumo_add_date))::int AS m,
    EXTRACT(DAY   FROM age(pneumo_dd_date, pneumo_add_date))::int AS d
) pned ON true

WHERE age_ans BETWEEN 65 AND 74
  AND NOT EXISTS (
  SELECT 1
  FROM unnest(patients_enrichis.tags_exclus) AS t(tag)
  WHERE list_contains(
    list_transform(coalesce(patients_enrichis.tags, []), x -> lower(x)),
    lower(tag)
    )
  )
  
ORDER BY Nom, "Prénom";

Liste des adultes de 75 ans et plus à convoquer pour mise à jour des vaccinations contre : dTP, pneumocoque, zona, VRS, covid 19 et grippe saisonnière

  • Sélection des patients : oui
  • Récupération des carnets : oui
  • Récupération des diagnostics : non

Algorithme : 

Est convoqué pour une dose  de : 

  • DTP si n'a pas reçu au moins 3 doses (ou dernière dose est un rappel) d'un vaccin au minimum avec valences diphtérie-tétanos-polio, avec une dernière dose 
    - à partir de l'âge de 70 ans si âge entre 75 et 84 ans ;
    - à partir de l'âge de 80 ans si âge entre 85 et 94 ans ;
  • Pneumocoque (voir détail de l'algorithme) ;
  • Zona si n'a pas reçu 2 doses ;
  • VRS si n'a reçu aucune dose ;
  • Covid 19 si n'a pas reçu 1 dose vaccinale depuis moins de 6 mois ;
  • Grippe si n'a pas reçu 1 dose vaccinale depuis moins de 6 mois.

Algorithme pneumocoque :

A jour si :

 ≥ 1 dose de 20-valent
  OU
≥ 1 dose de 21-valent
  OU
≥ 1 dose de 13-valent ET ≥ 1 dose de 23-valent ET dd = 23-valent ET dd depuis moins de 5 ans
  OU
≥ 1 dose de 15-valent ET ≥ 1 dose de 23-valent ET dd = 23-valent ET dd depuis moins de 5 ans
  OU
uniquement 7-valent ET dd depuis moins de 1 an
  OU
uniquement 13-valent ET dd depuis moins de 1 an
  OU
uniquement 15-valent ET dd depuis moins de 1 an
  OU
uniquement 23-valent ET dd depuis moins de 1 an

Si aucune de ces conditions n'est vérifiée : à faire.

La dernière colonne indique les maladies pour lesquelles le patient doit être convoqué.

L'export Excel des résultats de cette requête est prêt pour une exploitation en publipostage (par voie postale, e-mail ou SMS, tous les champs nécessaires étant présents).

WITH params AS (
  SELECT
    3    AS min_doses_dtp,
    183  AS delai_grippe_jours,
    183  AS delai_covid_jours,
    365  AS delai_pneumo_1an_jours,
    1825 AS delai_pneumo_5ans_jours,
    [''] AS tags_exclus                 -- Liste des tags à exclure
),

/* =========================
   DTP
   ========================= */
dtp_par_patient AS (
  WITH last_dtp AS (
    SELECT DISTINCT ON (v.patient_id)
      v.patient_id,
      v.performed_on AS derniere_dtp,
      v.booster      AS derniere_dtp_est_booster
    FROM vaccinations v
    WHERE v.vaccine_id IN (
      '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA (DTPCaHibHepB)
      '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON (DTPCaHibHepB)
      'ffc5a77a-87e1-420d-80c9-69e0b3fb9474', -- HEXAVAC (DTPCaHibHepB)
      'b9562145-be50-420f-a809-dd0e2762d142', -- VAXELIS (DTPCaHibHepB)
      'da117bfd-a4d3-4aec-9020-6a3cf1fa276a', -- INFANRIXQUINTA (DTCaPHib)
      '69f2e048-13fc-4848-8b91-39246baf2d93', -- PENTAVAC (DTCaPHib)
      '3ac78420-22e7-4ef4-90de-979a17726312', -- TETRAVAC-ACELLULAIRE (DTCaP)
      'c7d9d700-b481-4072-ab16-3b1aa7376298', -- INFANRIXTETRA (DTCaP)
      '384198db-b13c-4e08-b7a1-9311809a21b9', -- BOOSTRIXTETRA (dTcaP)
      '55adc664-d4a2-4154-80b2-dabd139fed28', -- REPEVAX (dTcaP)
      'ef50c7de-8bcd-4525-b2a0-e1047c376d83', -- TETRACOQ (DTCeP)
      '1a6e8b3b-dff8-421b-8892-8790e343127a', -- PENT-HIBEST (DTcePHib)
      '7fa97d99-ff52-41b8-b267-679d7b003620', -- PENTACOQ (DTcePHib)
      '3fddc43f-7d06-4186-8160-b42f230af0a7', -- Vaccin D.T.P. Pasteur (DTP)
      'd51d40bc-3646-4dc3-8cb9-e810ce2e1029', -- DTP - Diphtérie-Tétanos-Polio, sans précision (DTP)
      'f104fe51-74df-4b67-a85e-8acd32ad4a6c', -- REVAXIS (dTP)
      '768562aa-706b-4d6a-91e2-f49fe9ad3b96', -- Vaccin dTP - Diphtérie-Tétanos-Polio, dose réduite, sans précision (dTP)
      'd64aa9db-cb9e-4b55-997f-08a06cdcf87b', -- Vaccin hexavalent DTPCoqHibHepB, sans précision (DTPCoqHibHepB)
      'fdac4922-a30a-48d0-88bd-7c16c872528f', -- Vaccin DiphTP - Diphtérie-Tétanos-Polio, sans précision
      'e3981dfe-99e5-4092-b073-5639d6761e08', -- Vaccin pentavalent DTCoqHibP - Diphtérie-Tétanos-Coqueluche-Hib-Polio, sans précision
      'c9e71f76-f164-4246-92ea-d750c128f199'  -- Vaccin DiphTPCoq - Diphtérie-Tétanos-Coqueluche-Polio, sans précision
    )
    ORDER BY v.patient_id, v.performed_on DESC, v.id DESC
  )
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_dtp,
    MAX(v.performed_on) AS derniere_dtp,
    COALESCE(MAX(last_dtp.derniere_dtp_est_booster::int), 0)::boolean AS derniere_dtp_est_booster
  FROM vaccinations v
  LEFT JOIN last_dtp ON last_dtp.patient_id = v.patient_id
  WHERE v.vaccine_id IN (
      '246a6067-7c45-42af-bff9-90832bb6e4aa', -- INFANRIX HEXA (DTPCaHibHepB)
      '3ddb5821-9a52-428e-a40d-d065e4a630b1', -- HEXYON (DTPCaHibHepB)
      'ffc5a77a-87e1-420d-80c9-69e0b3fb9474', -- HEXAVAC (DTPCaHibHepB)
      'b9562145-be50-420f-a809-dd0e2762d142', -- VAXELIS (DTPCaHibHepB)
      'da117bfd-a4d3-4aec-9020-6a3cf1fa276a', -- INFANRIXQUINTA (DTCaPHib)
      '69f2e048-13fc-4848-8b91-39246baf2d93', -- PENTAVAC (DTCaPHib)
      '3ac78420-22e7-4ef4-90de-979a17726312', -- TETRAVAC-ACELLULAIRE (DTCaP)
      'c7d9d700-b481-4072-ab16-3b1aa7376298', -- INFANRIXTETRA (DTCaP)
      '384198db-b13c-4e08-b7a1-9311809a21b9', -- BOOSTRIXTETRA (dTcaP)
      '55adc664-d4a2-4154-80b2-dabd139fed28', -- REPEVAX (dTcaP)
      'ef50c7de-8bcd-4525-b2a0-e1047c376d83', -- TETRACOQ (DTCeP)
      '1a6e8b3b-dff8-421b-8892-8790e343127a', -- PENT-HIBEST (DTcePHib)
      '7fa97d99-ff52-41b8-b267-679d7b003620', -- PENTACOQ (DTcePHib)
      '3fddc43f-7d06-4186-8160-b42f230af0a7', -- Vaccin D.T.P. Pasteur (DTP)
      'd51d40bc-3646-4dc3-8cb9-e810ce2e1029', -- DTP - Diphtérie-Tétanos-Polio, sans précision (DTP)
      'f104fe51-74df-4b67-a85e-8acd32ad4a6c', -- REVAXIS (dTP)
      '768562aa-706b-4d6a-91e2-f49fe9ad3b96', -- Vaccin dTP - Diphtérie-Tétanos-Polio, dose réduite, sans précision (dTP)
      'd64aa9db-cb9e-4b55-997f-08a06cdcf87b', -- Vaccin hexavalent DTPCoqHibHepB, sans précision (DTPCoqHibHepB)
      'fdac4922-a30a-48d0-88bd-7c16c872528f', -- Vaccin DiphTP - Diphtérie-Tétanos-Polio, sans précision
      'e3981dfe-99e5-4092-b073-5639d6761e08', -- Vaccin pentavalent DTCoqHibP - Diphtérie-Tétanos-Coqueluche-Hib-Polio, sans précision
      'c9e71f76-f164-4246-92ea-d750c128f199'  -- Vaccin DiphTPCoq - Diphtérie-Tétanos-Coqueluche-Polio, sans précision
  )
  GROUP BY v.patient_id
),

/* =========================
   Grippe
   ========================= */
grippe_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_grippe,
    MAX(v.performed_on) AS derniere_grippe
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    'd5201c83-c7fe-48a4-b34c-f38e78f39cac', -- FLUCELVAX Trivalent
    '113c41b5-19c4-4788-811b-0fa81a18d636', -- INFLUVAC Trivalent
    '921a4957-762d-4ae4-ad70-bab26b28c5d2', -- VAXIGRIP Trivalent
    '9dd17477-32e9-4078-927c-90a7bd737628', -- VAXIGRIP ENFANTS Trivalent
    '7ae62689-ae48-41a1-afba-7d5a1e9f511d', -- EFLUELDA Trivalent
    'c39cdd50-8fec-40ef-8428-a05292f820d7'  -- FLUAD Trivalent
  )
  GROUP BY v.patient_id
),

/* =========================
   Covid 19
   ========================= */
covid_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_covid,
    MAX(v.performed_on) AS derniere_covid
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '5381126d-145a-49ee-bd87-c860a822fc5a', -- COMIRNATY KP.2 30 µg
    '97d043b8-a5ab-418a-bb41-326c8f5efd46'  -- COMIRNATY LP.8.1 30 µg
  )
  GROUP BY v.patient_id
),

/* =========================
   VRS
   ========================= */
vrs_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_vrs,
    MAX(v.performed_on) AS derniere_vrs
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '9ebc0156-9957-4a2b-80c6-77308a371edf', -- ABRYSVO
    '3e2c0656-f823-40ee-93e3-d1da121a636a'  -- AREXVY
  )
  GROUP BY v.patient_id
),

/* =========================
   Pneumo (règles "à faire" + ADD/DD)
   ========================= */
pneumo_par_patient AS (
  WITH pneumo_ranked AS (
    SELECT
      v.patient_id,
      v.performed_on,
      v.vaccine_id,
      row_number() OVER (
        PARTITION BY v.patient_id
        ORDER BY v.performed_on DESC, v.id DESC
      ) AS rn
    FROM vaccinations v
    WHERE v.vaccine_id IN (
      'b4248431-6211-43af-bf20-09b32daf38bc', -- PREVENAR-7 (PCV7)
      '5312b079-3245-49fe-9193-4658c4c5d1d0', -- PREVENAR 13 (PCV13)
      '5e6dd99c-ef7c-4bbd-8611-a3be793d8456', -- VAXNEUVANCE (PCV15)
      '197e5bd5-e048-40fb-8d8e-bcde2f04cb3e', -- PREVENAR 20 (PCV20)
      '64adbea9-8351-4f3d-995c-1b78a79abdfd', -- CAPVAXIVE (PCV21)
      'c3ae4d8f-73f7-4601-b4c6-972ba0113aa1', -- PNEUMOVAX (PPS23)
      'd9ff5075-be06-4baf-bdb0-3956f19b48b7'  -- PNEUMO 23 (PPS23)
    )
  )
  SELECT
    patient_id,

    COUNT(*) AS nbre_doses_pneumo,

    /* DD = dernière dose */
    MAX(performed_on) FILTER (WHERE rn = 1) AS pneumo_dd_date,
    MAX(vaccine_id)   FILTER (WHERE rn = 1) AS pneumo_dd_vaccine_id,

    /* ADD = dose avant dernière */
    MAX(performed_on) FILTER (WHERE rn = 2) AS pneumo_add_date,
    MAX(vaccine_id)   FILTER (WHERE rn = 2) AS pneumo_add_vaccine_id,

    /* Pour compat : "derniere_pneumo" = DD */
    MAX(performed_on) FILTER (WHERE rn = 1) AS derniere_pneumo,

    /* Compteurs par valence */
    SUM(CASE WHEN vaccine_id = 'b4248431-6211-43af-bf20-09b32daf38bc' THEN 1 ELSE 0 END) AS nbre_pcv7,
    SUM(CASE WHEN vaccine_id = '5312b079-3245-49fe-9193-4658c4c5d1d0' THEN 1 ELSE 0 END) AS nbre_pcv13,
    SUM(CASE WHEN vaccine_id = '5e6dd99c-ef7c-4bbd-8611-a3be793d8456' THEN 1 ELSE 0 END) AS nbre_pcv15,
    SUM(CASE WHEN vaccine_id = '197e5bd5-e048-40fb-8d8e-bcde2f04cb3e' THEN 1 ELSE 0 END) AS nbre_pcv20,
    SUM(CASE WHEN vaccine_id = '64adbea9-8351-4f3d-995c-1b78a79abdfd' THEN 1 ELSE 0 END) AS nbre_pcv21,
    SUM(CASE WHEN vaccine_id IN (
        'c3ae4d8f-73f7-4601-b4c6-972ba0113aa1',
        'd9ff5075-be06-4baf-bdb0-3956f19b48b7'
      ) THEN 1 ELSE 0 END) AS nbre_pps23,

    /* Dernière dose = 23-valent ? */
    (
      MAX(
        CASE
          WHEN vaccine_id IN (
            'c3ae4d8f-73f7-4601-b4c6-972ba0113aa1',
            'd9ff5075-be06-4baf-bdb0-3956f19b48b7'
          ) AND rn = 1
          THEN 1 ELSE 0
        END
      ) = 1
    ) AS derniere_pneumo_est_23,

    /* Valence DD */
    CASE MAX(vaccine_id) FILTER (WHERE rn = 1)
      WHEN 'b4248431-6211-43af-bf20-09b32daf38bc' THEN '7-valent'
      WHEN '5312b079-3245-49fe-9193-4658c4c5d1d0' THEN '13-valent'
      WHEN '5e6dd99c-ef7c-4bbd-8611-a3be793d8456' THEN '15-valent'
      WHEN '197e5bd5-e048-40fb-8d8e-bcde2f04cb3e' THEN '20-valent'
      WHEN '64adbea9-8351-4f3d-995c-1b78a79abdfd' THEN '21-valent'
      WHEN 'c3ae4d8f-73f7-4601-b4c6-972ba0113aa1' THEN '23-valent NC'
      WHEN 'd9ff5075-be06-4baf-bdb0-3956f19b48b7' THEN '23-valent NC'
      ELSE NULL
    END AS pneumo_dd_valence,

    /* Valence ADD */
    CASE MAX(vaccine_id) FILTER (WHERE rn = 2)
      WHEN 'b4248431-6211-43af-bf20-09b32daf38bc' THEN '7-valent'
      WHEN '5312b079-3245-49fe-9193-4658c4c5d1d0' THEN '13-valent'
      WHEN '5e6dd99c-ef7c-4bbd-8611-a3be793d8456' THEN '15-valent'
      WHEN '197e5bd5-e048-40fb-8d8e-bcde2f04cb3e' THEN '20-valent'
      WHEN '64adbea9-8351-4f3d-995c-1b78a79abdfd' THEN '21-valent'
      WHEN 'c3ae4d8f-73f7-4601-b4c6-972ba0113aa1' THEN '23-valent NC'
      WHEN 'd9ff5075-be06-4baf-bdb0-3956f19b48b7' THEN '23-valent NC'
      ELSE NULL
    END AS pneumo_add_valence

  FROM pneumo_ranked
  GROUP BY patient_id
),

/* =========================
   Zona
   ========================= */
zona_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_zona,
    MAX(v.performed_on) AS derniere_zona
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    'bd67a8e0-356c-497f-9f9d-6aeef99e92ba'  -- SHINGRIX
  )
  GROUP BY v.patient_id
),

patients_enrichis AS (
  SELECT
    p.id,
    p.last_name   AS Nom,
    p.first_names AS "Prénom",
    p.gender      AS Sexe,
    p.birth_date  AS "Date de naissance",
    p.tags,
    p.created_at  AS "Création du dossier",
    p.address1 AS "Adresse",
    p.address2 AS "Complément d'adresse",
    p.city_zip_code AS "Code postal",
    p.city AS "Commune",
    p.email AS "Email",
    p.email_2 AS "Email secondaire",
    p.phone_number AS "Téléphone",
    p.phone_number_2 AS "Téléphone secondaire",

    date_diff('year', p.birth_date, current_date) AS age_ans,

    coalesce(d.nbre_doses_dtp, 0) AS nbre_doses_dtp,
    d.derniere_dtp,
    coalesce(d.derniere_dtp_est_booster, false) AS derniere_dtp_est_booster,

    COALESCE(g.nbre_doses_grippe, 0) AS nbre_doses_grippe,
    g.derniere_grippe,

    COALESCE(cv.nbre_doses_covid, 0) AS nbre_doses_covid,
    cv.derniere_covid,

    /* VRS */
    COALESCE(vrs.nbre_doses_vrs, 0) AS nbre_doses_vrs,
    vrs.derniere_vrs,

    /* pneumo + détails nécessaires aux règles */
    COALESCE(pn.nbre_doses_pneumo, 0) AS nbre_doses_pneumo,
    pn.derniere_pneumo,
    pn.pneumo_add_valence,
    pn.pneumo_dd_valence,
    pn.pneumo_add_date,
    pn.pneumo_dd_date,
    COALESCE(pn.nbre_pcv7, 0)  AS nbre_pcv7,
    COALESCE(pn.nbre_pcv13, 0) AS nbre_pcv13,
    COALESCE(pn.nbre_pcv15, 0) AS nbre_pcv15,
    COALESCE(pn.nbre_pcv20, 0) AS nbre_pcv20,
    COALESCE(pn.nbre_pcv21, 0) AS nbre_pcv21,
    COALESCE(pn.nbre_pps23, 0) AS nbre_pps23,
    COALESCE(pn.derniere_pneumo_est_23, false) AS derniere_pneumo_est_23,

    COALESCE(z.nbre_doses_zona, 0) AS nbre_doses_zona,
    z.derniere_zona,

    /* ===== FLAGS ===== */
    (
      d.derniere_dtp IS NULL

      /* règle DTP par tranche d'âge */
      OR (
        age_ans BETWEEN 75 AND 84
        AND d.derniere_dtp < (p.birth_date + INTERVAL '70 years')
      )

      /* règle 65–74 ans : si dd avant 60 ans -> à faire */
      OR (
        age_ans BETWEEN 85 AND 94
        AND d.derniere_dtp < (p.birth_date + INTERVAL '80 years')
      )

      /* reste inchangé */
      OR (
        COALESCE(d.nbre_doses_dtp, 0) < params.min_doses_dtp
        AND COALESCE(d.derniere_dtp_est_booster, false) = false
      )
    ) AS dtp_a_faire,

    (
      g.derniere_grippe IS NULL
      OR date_diff('day', g.derniere_grippe, current_date) > params.delai_grippe_jours
    ) AS grippe_a_faire,

    (
      cv.derniere_covid IS NULL
      OR date_diff('day', cv.derniere_covid, current_date) > params.delai_covid_jours
    ) AS covid_a_faire,

    /* VRS : à faire si 0 dose */
    (COALESCE(vrs.nbre_doses_vrs, 0) < 1) AS vrs_a_faire,

    /* =========================
       Pneumo : A FAIRE (inverse de "à jour")
       ========================= */
    (
      NOT (
        /* A JOUR SI : ≥1 dose 20-valent */
        nbre_pcv20 >= 1

        /* OU : ≥1 dose 21-valent */
        OR nbre_pcv21 >= 1

        /* OU : PCV13 + PPS23, dernière = PPS23, et dd < 5 ans */
        OR (
          nbre_pcv13 >= 1
          AND nbre_pps23 >= 1
          AND derniere_pneumo_est_23 = true
          AND derniere_pneumo IS NOT NULL
          AND date_diff('day', derniere_pneumo, current_date) <= params.delai_pneumo_5ans_jours
        )

        /* OU : PCV15 + PPS23, dernière = PPS23, et dd < 5 ans */
        OR (
          nbre_pcv15 >= 1
          AND nbre_pps23 >= 1
          AND derniere_pneumo_est_23 = true
          AND derniere_pneumo IS NOT NULL
          AND date_diff('day', derniere_pneumo, current_date) <= params.delai_pneumo_5ans_jours
        )

        /* OU : uniquement PCV7, dd < 1 an */
        OR (
          nbre_pcv7 >= 1
          AND nbre_doses_pneumo = nbre_pcv7
          AND derniere_pneumo IS NOT NULL
          AND date_diff('day', derniere_pneumo, current_date) <= params.delai_pneumo_1an_jours
        )

        /* OU : uniquement PCV13, dd < 1 an */
        OR (
          nbre_pcv13 >= 1
          AND nbre_doses_pneumo = nbre_pcv13
          AND derniere_pneumo IS NOT NULL
          AND date_diff('day', derniere_pneumo, current_date) <= params.delai_pneumo_1an_jours
        )

        /* OU : uniquement PCV15, dd < 1 an */
        OR (
          nbre_pcv15 >= 1
          AND nbre_doses_pneumo = nbre_pcv15
          AND derniere_pneumo IS NOT NULL
          AND date_diff('day', derniere_pneumo, current_date) <= params.delai_pneumo_1an_jours
        )

        /* OU : uniquement PPS23, dd < 1 an */
        OR (
          nbre_pps23 >= 1
          AND nbre_doses_pneumo = nbre_pps23
          AND derniere_pneumo IS NOT NULL
          AND date_diff('day', derniere_pneumo, current_date) <= params.delai_pneumo_1an_jours
        )
      )
    ) AS pneumo_a_faire,

    (
      COALESCE(z.nbre_doses_zona, 0) < 2
    ) AS zona_a_faire,
    
    params.tags_exclus

  FROM patients p
  CROSS JOIN params
  LEFT JOIN dtp_par_patient d      ON d.patient_id = p.id
  LEFT JOIN grippe_par_patient g   ON g.patient_id = p.id
  LEFT JOIN covid_par_patient cv   ON cv.patient_id = p.id
  LEFT JOIN vrs_par_patient vrs    ON vrs.patient_id = p.id
  LEFT JOIN pneumo_par_patient pn  ON pn.patient_id = p.id
  LEFT JOIN zona_par_patient z     ON z.patient_id = p.id
)

SELECT
  Nom,
  "Prénom",
  Sexe,
  "Date de naissance",

  /* Âge lisible : années + mois (calcul DuckDB) */
  (
    CAST(date_diff('month', "Date de naissance", current_date) / 12 AS INTEGER) || 'a '
    || CAST(date_diff('month', "Date de naissance", current_date) % 12 AS INTEGER) || 'm'
  ) AS "Âge ce jour",

  "Adresse",
  "Complément d'adresse",
  "Code postal",
  "Commune",
  "Email",
  "Email secondaire",
  "Téléphone",
  "Téléphone secondaire",
  "Création du dossier",

  nbre_doses_dtp AS "Doses DTP",
  CASE
    WHEN derniere_dtp_est_booster THEN 'Oui'
    ELSE NULL
  END AS "dd DTP = Booster",

  CASE
    WHEN derniere_dtp IS NULL THEN NULL
    ELSE
      trim(
        BOTH ' ' FROM
          concat(
            CASE WHEN dtp.y > 0 THEN dtp.y || 'a ' ELSE '' END,
            CASE WHEN dtp.m > 0 THEN dtp.m || 'm ' ELSE '' END,
            dtp.d || 'j'
          )
      )
  END AS "Âge dd DTP",

  nbre_doses_grippe AS "Doses Grippe",
  nbre_doses_covid  AS "Doses Covid",

  nbre_doses_vrs    AS "Doses VRS",

  nbre_doses_pneumo AS "Doses Pneumo",
  CASE
    WHEN nbre_doses_pneumo >= 2 THEN pneumo_add_valence
    ELSE NULL
  END AS "Valence ADD",

  CASE
    WHEN nbre_doses_pneumo >= 1 THEN pneumo_dd_valence
    ELSE NULL
  END AS "Valence DD",

  CASE
    WHEN nbre_doses_pneumo >= 2
         AND pneumo_add_date IS NOT NULL
         AND pneumo_dd_date IS NOT NULL
    THEN
      trim(
        BOTH ' ' FROM
          concat(
            CASE WHEN pned.y > 0 THEN pned.y || 'a ' ELSE '' END,
            CASE WHEN pned.m > 0 THEN pned.m || 'm ' ELSE '' END,
            pned.d || 'j'
          )
      )
    ELSE NULL
  END AS "Écart ADD-DD",

  nbre_doses_zona   AS "Doses Zona",

  array_to_string(
    list_filter(
      list_value(
        CASE WHEN dtp_a_faire    THEN 'dtP' END,
        CASE WHEN grippe_a_faire THEN 'Grippe' END,
        CASE WHEN covid_a_faire  THEN 'Covid' END,
        CASE WHEN vrs_a_faire    THEN 'VRS' END,
        CASE WHEN pneumo_a_faire THEN 'Pneumo' END,
        CASE WHEN zona_a_faire   THEN 'Zona' END
      ),
      x -> x IS NOT NULL
    ),
    ', '
  ) AS "Vaccins à faire"

FROM patients_enrichis
LEFT JOIN LATERAL (
  SELECT
    EXTRACT(YEAR  FROM age(derniere_dtp, "Date de naissance"))::int AS y,
    EXTRACT(MONTH FROM age(derniere_dtp, "Date de naissance"))::int AS m,
    EXTRACT(DAY   FROM age(derniere_dtp, "Date de naissance"))::int AS d
) dtp ON true
LEFT JOIN LATERAL (
  SELECT
    EXTRACT(YEAR  FROM age(pneumo_dd_date, pneumo_add_date))::int AS y,
    EXTRACT(MONTH FROM age(pneumo_dd_date, pneumo_add_date))::int AS m,
    EXTRACT(DAY   FROM age(pneumo_dd_date, pneumo_add_date))::int AS d
) pned ON true

WHERE age_ans >= 75
  AND NOT EXISTS (
  SELECT 1
  FROM unnest(patients_enrichis.tags_exclus) AS t(tag)
  WHERE list_contains(
    list_transform(coalesce(patients_enrichis.tags, []), x -> lower(x)),
    lower(tag)
    )
  )
  
ORDER BY Nom, "Prénom";