Ceci est une alternative pour les utilisateurs ayant migré de Pricare, pour obtenir des listes qui permettent le contrôle sur les ES.
Ces requêtes ne concernent que la priorité 1.
Il faudra ajouter les nouveaux patients ou nouveaux diagnostics depuis la migration.
Avec le Reporting, faire un export qui comprend les tables repHe, repPatAdmin et repPatLink
1ère requête : Patients 45-64 ans, priorité 1, ES indiscutables : CISP K77, K86, K87, N86, R95, T82, T89, T90 et ICD N18.3, N18.4, N18.8, N18.9, N18.0, N19, P96.0.
Ces patients sont de toute façon à risque, quel que soit le degré de gravité de leur ES.
SELECT repPatAdmin.FName, repPatAdmin.LName, repPatAdmin.SEXE, repPatAdmin.BirthDate, repHe.MedicalCodeLight, repHe.CodeLabelFr, repPatAdmin.MTR, repPatAdmin.Dossier, repPatAdmin.PrivStreet, repPatAdmin.PrivNumber, repPatAdmin.PrivPostCode, repPatAdmin.PrivCity, repPatAdmin.NatNum, repPatAdmin.Phone1, repPatAdmin.Phone2, repPatAdmin.Phone3, repPatAdmin.Email
FROM (repHe INNER JOIN repPatLink ON repHe.PatId = repPatLink.PatIdMed) INNER JOIN repPatAdmin ON repPatLink.PatIdAdm = repPatAdmin.PatId
WHERE (((repPatAdmin.BirthDate) Between "19560101" And "19763112") AND ((repHe.MedicalCodeLight)="T90" Or (repHe.MedicalCodeLight)="T89" Or (repHe.MedicalCodeLight)="R95" Or (repHe.MedicalCodeLight)="K77" Or (repHe.MedicalCodeLight)="K86" Or (repHe.MedicalCodeLight)="K87" Or (repHe.MedicalCodeLight)="N86" Or (repHe.MedicalCodeLight)="T82") AND ((repHe.ActivityFr)="actif") AND ((repPatAdmin.Active)=True) AND ((repHe.IsPersonal)=True)) OR (((repPatAdmin.BirthDate) Between "19560101" And "19763112") AND ((repHe.ActivityFr)="actif") AND ((repPatAdmin.Active)=True) AND ((repHe.IsPersonal)=True) AND ((repHe.MedicalCodeId) Like "*icd10.N18.3*" Or (repHe.MedicalCodeId) Like "*icd10.N18.4*" Or (repHe.MedicalCodeId) Like "*icd10.N18.8*" Or (repHe.MedicalCodeId) Like "*icd10.N18.9*" Or (repHe.MedicalCodeId) Like "*icd10.N18.0*" Or (repHe.MedicalCodeId) Like "*icd10.N19*" Or (repHe.MedicalCodeId) Like "*icd10.P96.0*" Or (repHe.MedicalCodeId) Like "*10038257*" Or (repHe.MedicalCodeId) Like "*10063690*" Or (repHe.MedicalCodeId) Like "*10063694*" Or (repHe.MedicalCodeId) Like "*10063718*" Or (repHe.MedicalCodeId) Like "*10096195*" Or (repHe.MedicalCodeId) Like "*10101903*" Or (repHe.MedicalCodeId) Like "*10117683*" Or (repHe.MedicalCodeId) Like "*10119104*" Or (repHe.MedicalCodeId) Like "*10112495*" Or (repHe.MedicalCodeId) Like "*10113078*" Or (repHe.MedicalCodeId) Like "*10115915*" Or (repHe.MedicalCodeId) Like "*10121801*" Or (repHe.MedicalCodeId) Like "*10121916*" Or (repHe.MedicalCodeId) Like "*10122244*" Or (repHe.MedicalCodeId) Like "*10122612*" Or (repHe.MedicalCodeId) Like "*15002705*" Or (repHe.MedicalCodeId) Like "*15002705*" Or (repHe.MedicalCodeId) Like "*10000203*")) OR (((repPatAdmin.BirthDate) Between "19560101" And "19763112") AND ((repHe.ActivityFr)="actif") AND ((repPatAdmin.Active)=True) AND ((repHe.IsPersonal)=True) AND ((repHe.MedicalCodeId) Like "*10046900*" Or (repHe.MedicalCodeId) Like "*10046902*" Or (repHe.MedicalCodeId) Like "*10000203*" Or (repHe.MedicalCodeId) Like "*10046900*" Or (repHe.MedicalCodeId) Like "*10046902*" Or (repHe.MedicalCodeId) Like "*10003483*" Or (repHe.MedicalCodeId) Like "*10019868*" Or (repHe.MedicalCodeId) Like "*10022119*" Or (repHe.MedicalCodeId) Like "*10024240*" Or (repHe.MedicalCodeId) Like "*10025022*" Or (repHe.MedicalCodeId) Like "*10028074*" Or (repHe.MedicalCodeId) Like "*10046897*" Or (repHe.MedicalCodeId) Like "*10049487*" Or (repHe.MedicalCodeId) Like "*10072258*" Or (repHe.MedicalCodeId) Like "*10072266*" Or (repHe.MedicalCodeId) Like "*10072339*" Or (repHe.MedicalCodeId) Like "*10072599*" Or (repHe.MedicalCodeId) Like "*10086358*" Or (repHe.MedicalCodeId) Like "*10098476*" Or (repHe.MedicalCodeId) Like "*10101901*" Or (repHe.MedicalCodeId) Like "*10101930*" Or (repHe.MedicalCodeId) Like "*10102242*" Or (repHe.MedicalCodeId) Like "*10119404*"))
ORDER BY repPatAdmin.LName;
SELECT repPatAdmin.FName, repPatAdmin.LName, repPatAdmin.SEXE, repPatAdmin.BirthDate, repPatAdmin.MTR, repPatAdmin.Dossier, repPatAdmin.PrivStreet, repPatAdmin.PrivNumber, repPatAdmin.PrivPostCode, repPatAdmin.PrivCity, repPatAdmin.NatNum, repPatAdmin.Phone1, repPatAdmin.Phone2, repPatAdmin.Phone3, repPatAdmin.Email
FROM (repHe INNER JOIN repPatLink ON repHe.PatId = repPatLink.PatIdMed) INNER JOIN repPatAdmin ON repPatLink.PatIdAdm = repPatAdmin.PatId
WHERE (((repHe.MedicalCodeLight)="T90" Or (repHe.MedicalCodeLight)="T89" Or (repHe.MedicalCodeLight)="R95" Or (repHe.MedicalCodeLight)="K77" Or (repHe.MedicalCodeLight)="K86" Or (repHe.MedicalCodeLight)="K87" Or (repHe.MedicalCodeLight)="N86" Or (repHe.MedicalCodeLight)="T82") AND ((repHe.ActivityFr)="actif") AND ((repPatAdmin.Active)=True) AND ((repHe.IsPersonal)=True)) OR (((repHe.ActivityFr)="actif") AND ((repPatAdmin.Active)=True) AND ((repHe.IsPersonal)=True) AND ((repHe.MedicalCodeId) Like "*icd10.N18.3*" Or (repHe.MedicalCodeId) Like "*icd10.N18.4*" Or (repHe.MedicalCodeId) Like "*icd10.N18.8*" Or (repHe.MedicalCodeId) Like "*icd10.N18.9*" Or (repHe.MedicalCodeId) Like "*icd10.N18.0*" Or (repHe.MedicalCodeId) Like "*icd10.N19*" Or (repHe.MedicalCodeId) Like "*icd10.P96.0*" Or (repHe.MedicalCodeId) Like "*10038257*" Or (repHe.MedicalCodeId) Like "*10063690*" Or (repHe.MedicalCodeId) Like "*10063694*" Or (repHe.MedicalCodeId) Like "*10063718*" Or (repHe.MedicalCodeId) Like "*10096195*" Or (repHe.MedicalCodeId) Like "*10101903*" Or (repHe.MedicalCodeId) Like "*10117683*" Or (repHe.MedicalCodeId) Like "*10119104*" Or (repHe.MedicalCodeId) Like "*10112495*" Or (repHe.MedicalCodeId) Like "*10113078*" Or (repHe.MedicalCodeId) Like "*10115915*" Or (repHe.MedicalCodeId) Like "*10121801*" Or (repHe.MedicalCodeId) Like "*10121916*" Or (repHe.MedicalCodeId) Like "*10122244*" Or (repHe.MedicalCodeId) Like "*10122612*" Or (repHe.MedicalCodeId) Like "*15002705*" Or (repHe.MedicalCodeId) Like "*15002705*" Or (repHe.MedicalCodeId) Like "*10000203*")) OR (((repHe.ActivityFr)="actif") AND ((repPatAdmin.Active)=True) AND ((repHe.IsPersonal)=True) AND ((repHe.MedicalCodeId) Like "*10046900*" Or (repHe.MedicalCodeId) Like "*10046902*" Or (repHe.MedicalCodeId) Like "*10000203*" Or (repHe.MedicalCodeId) Like "*10046900*" Or (repHe.MedicalCodeId) Like "*10046902*" Or (repHe.MedicalCodeId) Like "*10003483*" Or (repHe.MedicalCodeId) Like "*10019868*" Or (repHe.MedicalCodeId) Like "*10022119*" Or (repHe.MedicalCodeId) Like "*10024240*" Or (repHe.MedicalCodeId) Like "*10025022*" Or (repHe.MedicalCodeId) Like "*10028074*" Or (repHe.MedicalCodeId) Like "*10046897*" Or (repHe.MedicalCodeId) Like "*10049487*" Or (repHe.MedicalCodeId) Like "*10072258*" Or (repHe.MedicalCodeId) Like "*10072266*" Or (repHe.MedicalCodeId) Like "*10072339*" Or (repHe.MedicalCodeId) Like "*10072599*" Or (repHe.MedicalCodeId) Like "*10086358*" Or (repHe.MedicalCodeId) Like "*10098476*" Or (repHe.MedicalCodeId) Like "*10101901*" Or (repHe.MedicalCodeId) Like "*10101930*" Or (repHe.MedicalCodeId) Like "*10102242*" Or (repHe.MedicalCodeId) Like "*10119404*"))
GROUP BY repPatAdmin.FName, repPatAdmin.LName, repPatAdmin.SEXE, repPatAdmin.BirthDate, repPatAdmin.MTR, repPatAdmin.Dossier, repPatAdmin.PrivStreet, repPatAdmin.PrivNumber, repPatAdmin.PrivPostCode, repPatAdmin.PrivCity, repPatAdmin.NatNum, repPatAdmin.Phone1, repPatAdmin.Phone2, repPatAdmin.Phone3, repPatAdmin.Email
HAVING (((repPatAdmin.BirthDate) Between "19560101" And "19763112" Or (repPatAdmin.BirthDate) Between "19560101" And "19763112" Or (repPatAdmin.BirthDate) Between "19560101" And "19763112"))
ORDER BY repPatAdmin.LName;
2ème requête : Patients 45-64 ans, priorité 1, ES à revoir en fonction du degré de gravité :
Plusieurs lignes possibles par patient
SELECT repPatAdmin.FName, repPatAdmin.LName, repPatAdmin.SEXE, repPatAdmin.BirthDate, repHe.MedicalCodeLight, repHe.CodeLabelFr, repPatAdmin.MTR, repPatAdmin.Dossier, repPatAdmin.PrivStreet, repPatAdmin.PrivNumber, repPatAdmin.PrivPostCode, repPatAdmin.PrivCity, repPatAdmin.NatNum, repPatAdmin.Phone1, repPatAdmin.Phone2, repPatAdmin.Phone3, repPatAdmin.Email
FROM (repHe INNER JOIN repPatLink ON repHe.PatId = repPatLink.PatIdMed) INNER JOIN repPatAdmin ON repPatLink.PatIdAdm = repPatAdmin.PatId
WHERE (((repPatAdmin.BirthDate) Between "19560101" And "19763112") AND ((repHe.MedicalCodeLight)="U28" Or (repHe.MedicalCodeLight)="U88" Or (repHe.MedicalCodeLight)="R28" Or (repHe.MedicalCodeLight)="R79" Or (repHe.MedicalCodeLight)="R96" Or (repHe.MedicalCodeLight)="K73" Or (repHe.MedicalCodeLight)="K74" Or (repHe.MedicalCodeLight)="K75" Or (repHe.MedicalCodeLight)="K76" Or (repHe.MedicalCodeLight)="K78" Or (repHe.MedicalCodeLight)="K82" Or (repHe.MedicalCodeLight)="K83" Or (repHe.MedicalCodeLight)="K84" Or (repHe.MedicalCodeLight)="K93" Or (repHe.MedicalCodeLight)="K91" Or (repHe.MedicalCodeLight)="K90" Or (repHe.MedicalCodeLight)="K92" Or (repHe.MedicalCodeLight)="P70" Or (repHe.MedicalCodeLight)="N28" Or (repHe.MedicalCodeLight)="N87" Or (repHe.MedicalCodeLight)="N88" Or (repHe.MedicalCodeLight)="N99" Or (repHe.MedicalCodeLight)="R84" Or (repHe.MedicalCodeLight)="R85" Or (repHe.MedicalCodeLight)="R92" Or (repHe.MedicalCodeLight)="U75" Or (repHe.MedicalCodeLight)="U76" Or (repHe.MedicalCodeLight)="U77" Or (repHe.MedicalCodeLight)="U79" Or (repHe.MedicalCodeLight)="K72" Or (repHe.MedicalCodeLight)="A79" Or (repHe.MedicalCodeLight)="D74" Or (repHe.MedicalCodeLight)="D75" Or (repHe.MedicalCodeLight)="D76" Or (repHe.MedicalCodeLight)="D77" Or (repHe.MedicalCodeLight)="F74" Or (repHe.MedicalCodeLight)="H75" Or (repHe.MedicalCodeLight)="L71" Or (repHe.MedicalCodeLight)="N74" Or (repHe.MedicalCodeLight)="N76" Or (repHe.MedicalCodeLight)="S77" Or (repHe.MedicalCodeLight)="T71" Or (repHe.MedicalCodeLight)="T73" Or (repHe.MedicalCodeLight)="W72" Or (repHe.MedicalCodeLight)="X75" Or (repHe.MedicalCodeLight)="X76" Or (repHe.MedicalCodeLight)="X77" Or (repHe.MedicalCodeLight)="X81" Or (repHe.MedicalCodeLight)="Y77" Or (repHe.MedicalCodeLight)="Y78") AND ((repHe.ActivityFr)="actif") AND ((repPatAdmin.Active)=True) AND ((repHe.IsPersonal)=True))
ORDER BY repPatAdmin.LName;
3ème requête : Patients 18-64 ans, priorité 1, ES à revoir en fonction du degré de gravité :
Plusieurs lignes possibles par patient.
Attention, il y a plusieurs ES sociaux très larges, il ne faut prendre en compte que la grande précarité (sans abri, patient incapable de se gérer…)
SELECT repPatAdmin.FName, repPatAdmin.LName, repPatAdmin.SEXE, repPatAdmin.BirthDate, repHe.MedicalCodeLight, repHe.CodeLabelFr, repPatAdmin.MTR, repPatAdmin.Dossier, repPatAdmin.PrivStreet, repPatAdmin.PrivNumber, repPatAdmin.PrivPostCode, repPatAdmin.PrivCity, repPatAdmin.NatNum, repHe.MedicalCodeId, repPatAdmin.Phone1, repPatAdmin.Phone2, repPatAdmin.Phone3, repPatAdmin.Email
FROM (repHe INNER JOIN repPatLink ON repHe.PatId = repPatLink.PatIdMed) INNER JOIN repPatAdmin ON repPatLink.PatIdAdm = repPatAdmin.PatId
WHERE (((repPatAdmin.BirthDate) Between "19560101" And "20033112") AND ((repHe.MedicalCodeLight)="B75" Or (repHe.MedicalCodeLight)="D97" Or (repHe.MedicalCodeLight)="D72" Or (repHe.MedicalCodeLight)="D81" Or (repHe.MedicalCodeLight)="P72" Or (repHe.MedicalCodeLight)="P73" Or (repHe.MedicalCodeLight)="N85" Or (repHe.MedicalCodeLight)="P85" Or (repHe.MedicalCodeLight)="P28" Or (repHe.MedicalCodeLight)="Z01" Or (repHe.MedicalCodeLight)="Z02" Or (repHe.MedicalCodeLight)="Z03" Or (repHe.MedicalCodeLight)="Z07" Or (repHe.MedicalCodeLight)="Z09" Or (repHe.MedicalCodeLight)="Z11" Or (repHe.MedicalCodeLight)="Z12" Or (repHe.MedicalCodeLight)="Z13" Or (repHe.MedicalCodeLight)="Z25" Or (repHe.MedicalCodeLight)="B90" Or (repHe.MedicalCodeLight)="B72" Or (repHe.MedicalCodeLight)="B73" Or (repHe.MedicalCodeLight)="B74") AND ((repHe.ActivityFr)="actif") AND ((repPatAdmin.Active)=True) AND ((repHe.IsPersonal)=True)) OR (((repPatAdmin.BirthDate) Between "19560101" And "20033112") AND ((repHe.MedicalCodeId) Like "* icd10.Q90.9*" Or (repHe.MedicalCodeId) Like "* icd10.N18.5*" Or (repHe.MedicalCodeId) Like "*10027107*" Or (repHe.MedicalCodeId) Like "*10050948*" Or (repHe.MedicalCodeId) Like "*10061607*" Or (repHe.MedicalCodeId) Like "*10124221*" Or (repHe.MedicalCodeId) Like "*15000949*") AND ((repHe.ActivityFr)="actif") AND ((repPatAdmin.Active)=True) AND ((repHe.IsPersonal)=True))
ORDER BY repPatAdmin.LName;