Aller au contenu principal

Exemples de requêtes SQL

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.

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).

Voir aussi :

Requêtes spéciales campagne de vaccination dans les collèges

Requêtes pour publipostage

Liste des requêtes disponibles :

Liste des patients asthmatiques

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

Toutes les conditions présentes dans le questionnaire du profil santé peuvent faire l'objet d'une sélection.

NB : ici la sélection s'effectue sur tous les patients asthmatiques mais il est possible de ne sélectionner que les asthmatiques sévères par exemple.

SELECT
  p.last_name                AS "Nom",
  p.first_names              AS "Prénoms",
  p.birth_date               AS "Date de naissance",
  p.gender                   AS "Sexe",
FROM
  patients p
WHERE
  (
    -- Asthme sévère sous traitement continu
    p.health_profile ->> 'e131514e-7cb3-4736-836d-6105d66748fe') = TRUE
  OR 
  (
    -- Asthme (autre que sévère et sous traitement continu)
    p.health_profile ->> 'fba6d958-bd86-46e0-9668-47d7d7fc896c') = TRUE;

Couverture vaccinale contre la grippe saisonnière des personnes âgées de 65 ans ou plus

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

NB : à jour si dernière dose administrée depuis moins de 8 mois.

Ligne 3, modifier si nécessaire "65 years" par l'âge limite inférieure souhaité.
Ligne 4, modifier si nécessaire "8 months" par délai souhaité (nombre de mois maximum pour déclarer une vaccination valide).

WITH params AS (
  SELECT
    INTERVAL '65 years'  AS age_seuil,    -- Seuil d'âge (modifiable)
    INTERVAL '8 months'  AS delai_grippe  -- Validité vaccin grippal (modifiable)
),

patients_65_plus AS (
  SELECT
    p.id AS patient_id
  FROM patients p
  CROSS JOIN params par
  WHERE
    p.birth_date <= CURRENT_DATE - par.age_seuil
),

last_grippe_dose AS (
  SELECT
    v.patient_id,
    MAX(v.performed_on) AS last_dose_date
  FROM vaccinations v
  WHERE
    v.vaccine_id IN (
      '921a4957-762d-4ae4-ad70-bab26b28c5d2', -- Vaxigrip
      '113c41b5-19c4-4788-811b-0fa81a18d636', -- Influvac
      'c39cdd50-8fec-40ef-8428-a05292f820d7', -- Fluad
      '7ae62689-ae48-41a1-afba-7d5a1e9f511d', -- Efluelda
      'd5201c83-c7fe-48a4-b34c-f38e78f39cac'  -- Flucelvax
    )
  GROUP BY v.patient_id
),

grippe_a_jour AS (
  SELECT
    l.patient_id
  FROM last_grippe_dose l
  CROSS JOIN params par
  WHERE
    l.last_dose_date >= CURRENT_DATE - par.delai_grippe
)

SELECT
  COUNT(p.patient_id) AS "Nbre de patients 65 ans ou plus",

  COUNT(g.patient_id) AS "A jour",

  COUNT(p.patient_id) - COUNT(g.patient_id) AS "Pas à jour",

  ROUND(
    100.0 * COUNT(g.patient_id)
    / NULLIF(COUNT(p.patient_id), 0),
    1
  ) AS "Pourcentage à jour"

FROM patients_65_plus p
LEFT JOIN grippe_a_jour g
  ON g.patient_id = p.patient_id;

Couverture vaccinale contre la grippe des patients diabétiques de 18 à 64 ans inclus

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

NB : à jour si dernière dose administrée depuis moins de 8 mois.

Modifiez les paramètres si nécessaire.

WITH params AS (
  SELECT
    INTERVAL '18 years'  AS age_min,      -- Seuil d'âge minimum inclus (modifiable)
    INTERVAL '65 years'  AS age_max,      -- Seuil d'âge maximum exclus (modifiable)
    INTERVAL '8 months'  AS delai_grippe  -- Validité vaccin grippal (modifiable)
),

patients_18_64_diabetiques AS (
  SELECT
    p.id AS patient_id
  FROM patients p
  CROSS JOIN params par
  WHERE
    p.birth_date <= CURRENT_DATE - par.age_min
    AND p.birth_date > CURRENT_DATE - par.age_max
    AND (
      (p.health_profile ->> 'f8cedc4a-283b-43f0-ae53-196bbcb5d19a') = 'true'
      OR
      (p.health_profile ->> '74a61dc8-f817-42bb-8604-87da0711d03e') = 'true'
    )
),

last_grippe_dose AS (
  SELECT
    v.patient_id,
    MAX(v.performed_on) AS last_dose_date
  FROM vaccinations v
  WHERE
    v.vaccine_id IN (
      '921a4957-762d-4ae4-ad70-bab26b28c5d2', -- Vaxigrip
      '113c41b5-19c4-4788-811b-0fa81a18d636', -- Influvac
      'c39cdd50-8fec-40ef-8428-a05292f820d7', -- Fluad
      '7ae62689-ae48-41a1-afba-7d5a1e9f511d', -- Efluelda
      'd5201c83-c7fe-48a4-b34c-f38e78f39cac'  -- Flucelvax
    )
  GROUP BY v.patient_id
),

grippe_a_jour AS (
  SELECT
    l.patient_id
  FROM last_grippe_dose l
  CROSS JOIN params par
  WHERE
    l.last_dose_date >= CURRENT_DATE - par.delai_grippe
)

SELECT
  COUNT(p.patient_id) AS "Nbre de patients diabétiques de 18 à 64 ans",
  COUNT(g.patient_id) AS "A jour",
  COUNT(p.patient_id) - COUNT(g.patient_id) AS "Pas à jour",
  ROUND(
    100.0 * COUNT(g.patient_id)
    / NULLIF(COUNT(p.patient_id), 0),
    1
  ) AS "Couverture vaccinale"

FROM patients_18_64_diabetiques p
LEFT JOIN grippe_a_jour g
  ON g.patient_id = p.patient_id;

Liste des dossiers en doublon

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

Doublons stricts : même nom, prénom(s) et date de naissance. C’est le critère de référence pour détecter des doublons de création accidentelle.

SELECT
  upper(trim(last_name)) AS nom,
  upper(substr(lower(trim(first_names)), 1, 1))
  || substr(lower(trim(first_names)), 2) AS prenom,

  birth_date,
  COUNT(*) AS nb_dossiers,
  ARRAY_AGG(id ORDER BY created_at) AS patient_ids,
  ARRAY_AGG(created_at ORDER BY created_at) AS dates_creation
FROM patients
GROUP BY
  upper(trim(last_name)),
  lower(trim(first_names)),
  birth_date
HAVING COUNT(*) > 1
ORDER BY nb_dossiers DESC, nom, prenom;

Doublons probables : avec nom + date de naissance, prénoms différents (permet de retrouver des dossiers avec une faute d'orthographe dans le prénom mais attention : ne distingue pas les jumeaux)

Ex : Jean Paul / Jean-Paul / Jean P.

SELECT
  upper(trim(last_name)) AS nom,
  birth_date,
  COUNT(*) AS nb_dossiers,
  ARRAY_AGG(
    first_names
    ORDER BY created_at
  ) AS dossiers
FROM patients
GROUP BY
  upper(trim(last_name)),
  birth_date
HAVING COUNT(*) > 1
ORDER BY nb_dossiers DESC;

Liste des nourrissons âgés de 6 mois n'ayant pas été vaccinés par NIMERIX

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

NB : 6 mois et pas 7
Ligne 3 et 4, modifier si nécessaire les limites d'âge souhaitées.

WITH params AS (
  SELECT
    6  AS min_months,
    7  AS max_months,
    '38fef5bc-4f68-4292-aa82-c45042ab4af4'::uuid AS vaccine_id
)

SELECT
  p.id,
  p.last_name,
  p.first_names,
  p.gender,
  p.birth_date,
  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"
  
FROM
  patients p
CROSS JOIN params prm
LEFT JOIN vaccinations v
  ON p.id = v.patient_id
  AND v.vaccine_id = prm.vaccine_id
WHERE
  v.id IS NULL
  AND p.birth_date <= CURRENT_DATE - INTERVAL '1 month' * prm.min_months
  AND p.birth_date >  CURRENT_DATE - INTERVAL '1 month' * prm.max_months
ORDER BY
  p.last_name,
  p.first_names;

Liste des enfants de 6 à 10 ans avec marqueur de validité du schéma vaccinal ROR

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

NB : pour chaque enfant, nombre de doses reçues, âge lors de la première dose et validité du schéma. Un schéma est valide si 2 doses avec d1 à partir de 12 mois ou 3 doses si d1 entre 6 et 11 mois révolus.
Ligne 3 et 4, modifier si nécessaire les limites d'âge souhaitées pour la validité du schéma.
Ligne 5 et 6 : filtre d'âge pour la liste.

WITH params AS (
  SELECT
    12 AS age_min_ror_2d_mois,   -- 2 doses si D1 ≥ 12 mois
     6 AS age_min_ror_3d_mois,   -- 3 doses si D1 ≥ 6 mois
    72  AS age_min_patient_mois, -- 6 ans
    120 AS age_max_patient_mois  -- 10 ans
),

ror_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_ror,
    MIN(v.performed_on) AS date_premiere_dose_ror,
    MAX(v.performed_on) AS date_derniere_dose_ror
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '9435556b-4fce-44f7-81d6-e5d2d240860b', -- Priorix
    '9a08067b-3ec7-4229-a6f6-0803049ee699'  -- MMR-VaxPro
  )
  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.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",

    r.nbre_doses_ror,
    r.date_premiere_dose_ror,
    r.date_derniere_dose_ror,

    params.age_min_ror_2d_mois,
    params.age_min_ror_3d_mois,
    params.age_min_patient_mois,
    params.age_max_patient_mois
  FROM patients p
  CROSS JOIN params
  LEFT JOIN ror_par_patient r ON r.patient_id = p.id
  WHERE
    -- Filtre âge patient : [min, max[
    date_diff('month', p.birth_date, current_date) >= params.age_min_patient_mois
    AND date_diff('month', p.birth_date, current_date) <  params.age_max_patient_mois
)

SELECT
  Nom,
  Prénom,
  Sexe,
  "Date de naissance",
  CAST(date_diff('month', "Date de naissance", current_date) / 12 AS INTEGER)
  || ' ans ' ||
  CAST(date_diff('month', "Date de naissance", current_date) % 12 AS INTEGER)
  || ' mois' AS "Âge ce jour",
  "Création du dossier",
  "Adresse",
  "Complément d'adresse",
  "Code postal",
  "Commune",
  "Email",
  "Email secondaire",
  "Téléphone",
  "Téléphone secondaire",
    
  nbre_doses_ror AS "Nb doses ROR",
  CASE
  WHEN date_premiere_dose_ror IS NOT NULL
  THEN date_diff(
         'month',
         "Date de naissance",
         date_premiere_dose_ror
       )
  ELSE NULL
  END AS "Âge à la 1ère dose ROR (mois)",
  CASE
    WHEN nbre_doses_ror = 2
     AND date_diff('month', "Date de naissance", date_premiere_dose_ror) >= age_min_ror_2d_mois
    THEN '✓'
    WHEN nbre_doses_ror >= 3
     AND date_diff('month', "Date de naissance", date_premiere_dose_ror) >= age_min_ror_3d_mois
    THEN '✓'
    ELSE '-'
  END AS "Schéma valide ROR",

  CASE
  WHEN
      nbre_doses_ror IS NULL
      OR nbre_doses_ror = 0
      OR nbre_doses_ror = 1
      OR (
        nbre_doses_ror = 2
        AND date_diff('month', "Date de naissance", date_premiere_dose_ror) < age_min_ror_2d_mois
      )
      OR (
        nbre_doses_ror >= 3
        AND date_diff('month', "Date de naissance", date_premiere_dose_ror) < age_min_ror_3d_mois
      )
  THEN '✓'
  ELSE '-'
  END AS "ROR à faire"

FROM patients_enrichis

ORDER BY
  Nom,
  Prénom;

Liste des enfants de 6 à 10 ans qui ne sont pas à jour de la vaccination contre rougeole, oreillons et rubéole

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

NB : ne conserve de la liste ci-dessus que les enfants à vacciner.
Ligne 3 et 4, modifier si nécessaire les limites d'âge souhaitées pour la validité des schémas.
Ligne 5 et6 : filtre d'âge pour la liste.

WITH params AS (
  SELECT
    12 AS age_min_ror_2d_mois,   -- 2 doses si D1 ≥ 12 mois
     6 AS age_min_ror_3d_mois,   -- 3 doses si D1 ≥ 6 mois
    72  AS age_min_patient_mois, -- 6 ans
    120 AS age_max_patient_mois  -- 10 ans
),

ror_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_ror,
    MIN(v.performed_on) AS date_premiere_dose_ror,
    MAX(v.performed_on) AS date_derniere_dose_ror
  FROM vaccinations v
  WHERE v.vaccine_id IN (
    '9435556b-4fce-44f7-81d6-e5d2d240860b', -- Priorix
    '9a08067b-3ec7-4229-a6f6-0803049ee699'  -- MMR-VaxPro
  )
  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.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",

    r.nbre_doses_ror,
    r.date_premiere_dose_ror,

    params.age_min_ror_2d_mois,
    params.age_min_ror_3d_mois
  FROM patients p
  CROSS JOIN params
  LEFT JOIN ror_par_patient r ON r.patient_id = p.id
  WHERE
    date_diff('month', p.birth_date, current_date) >= params.age_min_patient_mois
    AND date_diff('month', p.birth_date, current_date) <  params.age_max_patient_mois
),

resultats AS (
  SELECT
    Nom,
    Prénom,
    Sexe,
    "Date de naissance",
    CAST(date_diff('month', "Date de naissance", current_date) / 12 AS INTEGER)
    || ' ans ' ||
    CAST(date_diff('month', "Date de naissance", current_date) % 12 AS INTEGER)
    || ' mois' AS "Âge ce jour",
    "Création du dossier",
    "Adresse",
    "Complément d'adresse",
    "Code postal",
    "Commune",
    "Email",
    "Email secondaire",
    "Téléphone",
    "Téléphone secondaire",

    nbre_doses_ror AS "Nb doses ROR",

    CASE
      WHEN date_premiere_dose_ror IS NOT NULL
      THEN date_diff('month', "Date de naissance", date_premiere_dose_ror)
      ELSE NULL
    END AS "Âge à la 1ère dose ROR (mois)",

    CASE
      WHEN
        nbre_doses_ror IS NULL
        OR nbre_doses_ror = 0
        OR nbre_doses_ror = 1
        OR (
          nbre_doses_ror = 2
          AND date_diff('month', "Date de naissance", date_premiere_dose_ror) < age_min_ror_2d_mois
        )
        OR (
          nbre_doses_ror >= 3
          AND date_diff('month', "Date de naissance", date_premiere_dose_ror) < age_min_ror_3d_mois
        )
      THEN '✓'
      ELSE '-'
    END AS "ROR à faire"

  FROM patients_enrichis
)

SELECT *
FROM resultats
WHERE "ROR à faire" = '✓'
ORDER BY
  Nom,
  Prénom;

Liste des séniors âgés de 65 à 70 ans qui ne sont pas à jour de la vaccination contre le zona

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

NB : ne conserve de la liste ci-dessus que les enfants à vacciner.
Ligne 5 et6 : filtre d'âge pour la liste. 852 mois pour inclure 70 ans.

WITH params AS (
  SELECT
    780 AS age_min_patient_mois, -- 65 ans
    840 AS age_max_patient_mois  -- 70 ans (exclus)
),

shingrix_par_patient AS (
  SELECT
    v.patient_id,
    COUNT(*) AS nbre_doses_shingrix,
    MIN(v.performed_on) AS date_premiere_dose_shingrix,
    MAX(v.performed_on) AS date_derniere_dose_shingrix
  FROM vaccinations v
  WHERE v.vaccine_id = '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.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",

    s.nbre_doses_shingrix,
    s.date_premiere_dose_shingrix,
    s.date_derniere_dose_shingrix
  FROM patients p
  CROSS JOIN params
  LEFT JOIN shingrix_par_patient s ON s.patient_id = p.id
  WHERE
    -- Filtre âge : [65, 70[
    date_diff('month', p.birth_date, current_date) >= params.age_min_patient_mois
    AND date_diff('month', p.birth_date, current_date) <  params.age_max_patient_mois
)

SELECT
  Nom,
  Prénom,
  Sexe,
  "Date de naissance",
  CAST(date_diff('month', "Date de naissance", current_date) / 12 AS INTEGER)
  || ' ans ' ||
  CAST(date_diff('month', "Date de naissance", current_date) % 12 AS INTEGER)
  || ' mois' AS "Âge ce jour",
  "Création du dossier",
  "Adresse",
  "Complément d'adresse",
  "Code postal",
  "Commune",
  "Email",
  "Email secondaire",
  "Téléphone",
  "Téléphone secondaire",

  COALESCE(nbre_doses_shingrix, 0) AS "Nb doses Shingrix",

  CASE
    WHEN date_premiere_dose_shingrix IS NOT NULL
    THEN date_diff(
           'month',
           "Date de naissance",
           date_premiere_dose_shingrix
         )
    ELSE NULL
  END AS "Âge à la 1ère dose Shingrix (mois)"

FROM patients_enrichis
WHERE
  COALESCE(nbre_doses_shingrix, 0) < 2
ORDER BY
  Nom,
  Prénom;