  7/8 Ensemble d'indicateurs 2/2 #### saisie #### Début de la période|debut_periode|date| Fin de la période|fin_periode|date| #### sql #### nb_conseils_hm<-SELECT COUNT(*) FROM Depi WHERE ( Dat_ BETWEEN debut_periode AND fin_periode ) AND Sexe=1 nb_conseils_fm<-SELECT COUNT(*) FROM Depi WHERE ( Dat_ BETWEEN debut_periode AND fin_periode ) AND Sexe=2 nb_conseils<-SELECT COUNT(*) FROM Depi WHERE ( Dat_ BETWEEN debut_periode AND fin_periode ) nb_tests_hm<-SELECT COUNT(*) FROM Depi WHERE ( TestDat_ BETWEEN debut_periode AND fin_periode ) AND Sexe=1 nb_tests_fm<-SELECT COUNT(*) FROM Depi WHERE ( TestDat_ BETWEEN debut_periode AND fin_periode ) AND Sexe=2 nb_tests<-SELECT COUNT(*) FROM Depi WHERE ( TestDat_ BETWEEN debut_periode AND fin_periode ) nb_retraits_hm<-SELECT COUNT(*) FROM Depi WHERE ( TestRetr BETWEEN debut_periode AND fin_periode ) AND Sexe=1 nb_retraits_fm<-SELECT COUNT(*) FROM Depi WHERE ( TestRetr BETWEEN debut_periode AND fin_periode ) AND Sexe=2 nb_retraits<-SELECT COUNT(*) FROM Depi WHERE ( TestRetr BETWEEN debut_periode AND fin_periode ) nb_cd4_mois_hm<-SELECT COUNT(Entr.Nume) / DATEDIFF( fin_periode , debut_periode ) *30 FROM Entr,Doss WHERE ( LaboDat_ BETWEEN debut_periode AND fin_periode ) AND Cd40>0 AND Entr.Doss=Doss.Nume AND RensSexe=1 nb_cd4_mois_fm<-SELECT COUNT(Entr.Nume) / DATEDIFF( fin_periode , debut_periode ) *30 FROM Entr,Doss WHERE ( LaboDat_ BETWEEN debut_periode AND fin_periode ) AND Cd40>0 AND Entr.Doss=Doss.Nume AND RensSexe=2 nb_cd4_mois<-SELECT COUNT(*) / DATEDIFF( fin_periode , debut_periode ) *30 FROM Entr WHERE ( LaboDat_ BETWEEN debut_periode AND fin_periode ) AND Cd40>0 nb_doss_elig_arv_hm_enf<-SELECT COUNT(DISTINCT Doss.Nume) FROM Doss,MediCons,MediConsCond WHERE ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND Doss.Nume=MediCons.Doss AND MediCons.Cond=MediConsCond.Nume AND MediConsCond.Desi="A mettre sous ARV" AND Doss.Arv_Desi="" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=1 AND RensAge_<15 nb_doss_elig_arv_hm<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons,MediConsCond WHERE ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND Doss.Nume=MediCons.Doss AND MediCons.Cond=MediConsCond.Nume AND MediConsCond.Desi="A mettre sous ARV" AND Doss.Arv_Desi="" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=1 AND RensAge_>=15 nb_doss_elig_arv_fm_enf<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons,MediConsCond WHERE ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND Doss.Nume=MediCons.Doss AND MediCons.Cond=MediConsCond.Nume AND MediConsCond.Desi="A mettre sous ARV" AND Doss.Arv_Desi="" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=2 AND RensAge_<15 nb_doss_elig_arv_fm<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons,MediConsCond WHERE ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND Doss.Nume=MediCons.Doss AND MediCons.Cond=MediConsCond.Nume AND MediConsCond.Desi="A mettre sous ARV" AND Doss.Arv_Desi="" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=2 AND RensAge_>=15 nb_arv_hm_enf<-SELECT COUNT( DISTINCT MediCons.Doss ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Doss.RensSexe=1 AND Doss.RensAge_<15 nb_arv_hm<-SELECT COUNT( DISTINCT MediCons.Doss ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Doss.RensSexe=1 AND Doss.RensAge_>=15 nb_arv_fm_enf<-SELECT COUNT( DISTINCT MediCons.Doss ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Doss.RensSexe=2 AND Doss.RensAge_<15 nb_arv_fm<-SELECT COUNT( DISTINCT MediCons.Doss ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Doss.RensSexe=2 AND Doss.RensAge_>=15 nb_doss_arv1_hm_enf<-SELECT COUNT( DISTINCT MediCons.Doss ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Arv_Lign<=1 AND Doss.RensSexe=1 AND Doss.RensAge_<15 nb_doss_arv1_hm<-SELECT COUNT( DISTINCT MediCons.Doss ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Arv_Lign<=1 AND Doss.RensSexe=1 AND Doss.RensAge_>=15 nb_doss_arv1_fm_enf<-SELECT COUNT( DISTINCT MediCons.Doss ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Arv_Lign<=1 AND Doss.RensSexe=2 AND Doss.RensAge_<15 nb_doss_arv1_fm<-SELECT COUNT( DISTINCT MediCons.Doss ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Arv_Lign<=1 AND Doss.RensSexe=2 AND Doss.RensAge_>=15 nb_doss_arv2_hm_enf<-SELECT COUNT( DISTINCT MediCons.Doss ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Arv_Lign=2 AND Doss.RensSexe=1 AND Doss.RensAge_<15 nb_doss_arv2_hm<-SELECT COUNT( DISTINCT MediCons.Doss ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Arv_Lign=2 AND Doss.RensSexe=1 AND Doss.RensAge_>=15 nb_doss_arv2_fm_enf<-SELECT COUNT( DISTINCT MediCons.Doss ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Arv_Lign=2 AND Doss.RensSexe=2 AND Doss.RensAge_<15 nb_doss_arv2_fm<-SELECT COUNT( DISTINCT MediCons.Doss ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Arv_Lign=2 AND Doss.RensSexe=2 AND Doss.RensAge_>=15 SET @nb_mois=3 DROP TABLE IF EXISTS Temp CREATE TEMPORARY TABLE Temp (Doss INT) INSERT INTO Temp (Doss) SELECT DISTINCT Doss FROM MediCons WHERE Dat_>=DATE_SUB( fin_periode ,INTERVAL @nb_mois MONTH) INSERT INTO Temp (Doss) SELECT DISTINCT Doss FROM SociCons WHERE Dat_>=DATE_SUB( fin_periode ,INTERVAL @nb_mois MONTH) INSERT INTO Temp (Doss) SELECT DISTINCT Doss FROM Psy_Cons WHERE Dat_>=DATE_SUB( fin_periode ,INTERVAL @nb_mois MONTH) INSERT INTO Temp (Doss) SELECT DISTINCT Doss FROM ObseCons WHERE Dat_>=DATE_SUB( fin_periode ,INTERVAL @nb_mois MONTH) INSERT INTO Temp (Doss) SELECT DISTINCT Doss FROM Item WHERE ( Dat_ BETWEEN debut_periode AND fin_periode ) AND Dest=6 INSERT INTO Temp (Doss) SELECT DISTINCT Doss FROM Entr WHERE ArriHoro>=DATE_SUB( fin_periode ,INTERVAL @nb_mois MONTH) nb_arv_pdv_hm_enf<-SELECT COUNT( DISTINCT Nume ) FROM Doss LEFT JOIN Temp ON Doss.Nume=Temp.Doss WHERE Temp.Doss IS NULL AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=1 AND RensAge_<15 AND RensVoya<>1 nb_arv_pdv_hm<-SELECT COUNT( DISTINCT Nume ) FROM Doss LEFT JOIN Temp ON Doss.Nume=Temp.Doss WHERE Temp.Doss IS NULL AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=1 AND RensAge_>=15 AND RensVoya<>1 nb_arv_pdv_fm_enf<-SELECT COUNT( DISTINCT Nume ) FROM Doss LEFT JOIN Temp ON Doss.Nume=Temp.Doss WHERE Temp.Doss IS NULL AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=2 AND RensAge_<15 AND RensVoya<>1 nb_arv_pdv_fm<-SELECT COUNT( DISTINCT Nume ) FROM Doss LEFT JOIN Temp ON Doss.Nume=Temp.Doss WHERE Temp.Doss IS NULL AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=2 AND RensAge_>=15 AND RensVoya<>1 nb_surv6_hm_enf<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND MediCons.Dat_<=DATE_SUB( fin_periode ,INTERVAL 6 MONTH) AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=1 AND RensAge_<15 nb_surv6_hm<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND MediCons.Dat_<=DATE_SUB( fin_periode ,INTERVAL 6 MONTH) AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=1 AND RensAge_>=15 nb_surv6_fm_enf<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND MediCons.Dat_<=DATE_SUB( fin_periode ,INTERVAL 6 MONTH) AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=2 AND RensAge_<15 nb_surv6_fm<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND MediCons.Dat_<=DATE_SUB( fin_periode ,INTERVAL 6 MONTH) AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=2 AND RensAge_>=15 nb_surv12_hm_enf<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND MediCons.Dat_<=DATE_SUB( fin_periode ,INTERVAL 12 MONTH) AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=1 AND RensAge_<15 nb_surv12_hm<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND MediCons.Dat_<=DATE_SUB( fin_periode ,INTERVAL 12 MONTH) AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=1 AND RensAge_>=15 nb_surv12_fm_enf<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND MediCons.Dat_<=DATE_SUB( fin_periode ,INTERVAL 12 MONTH) AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=2 AND RensAge_<15 nb_surv12_fm<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND MediCons.Dat_<=DATE_SUB( fin_periode ,INTERVAL 12 MONTH) AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=2 AND RensAge_>=15 nb_surv24_hm_enf<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND MediCons.Dat_<=DATE_SUB( fin_periode ,INTERVAL 24 MONTH) AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=1 AND RensAge_<15 nb_surv24_hm<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND MediCons.Dat_<=DATE_SUB( fin_periode ,INTERVAL 24 MONTH) AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=1 AND RensAge_>=15 nb_surv24_fm_enf<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND MediCons.Dat_<=DATE_SUB( fin_periode ,INTERVAL 24 MONTH) AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=2 AND RensAge_<15 nb_surv24_fm<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND MediCons.Dat_<=DATE_SUB( fin_periode ,INTERVAL 24 MONTH) AND Doss.OuvrDat_<= fin_periode AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensSexe=2 AND RensAge_>=15 nb_rupt_hm_enf<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ((Arv0Prsc<>"" AND (Arv0Serv IS NULL OR Arv0Serv=0)) OR (Arv1Prsc<>"" AND (Arv1Serv IS NULL OR Arv1Serv=0)) OR (Arv2Prsc<>"" AND (Arv2Serv IS NULL OR Arv2Serv=0)) OR (Arv3Prsc<>"" AND (Arv3Serv IS NULL OR Arv3Serv=0))) AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND Doss.RensSexe=1 AND Doss.RensAge_<15 nb_rupt_hm<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ((Arv0Prsc<>"" AND (Arv0Serv IS NULL OR Arv0Serv=0)) OR (Arv1Prsc<>"" AND (Arv1Serv IS NULL OR Arv1Serv=0)) OR (Arv2Prsc<>"" AND (Arv2Serv IS NULL OR Arv2Serv=0)) OR (Arv3Prsc<>"" AND (Arv3Serv IS NULL OR Arv3Serv=0))) AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND Doss.RensSexe=1 AND Doss.RensAge_>=15 nb_rupt_fm_enf<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ((Arv0Prsc<>"" AND (Arv0Serv IS NULL OR Arv0Serv=0)) OR (Arv1Prsc<>"" AND (Arv1Serv IS NULL OR Arv1Serv=0)) OR (Arv2Prsc<>"" AND (Arv2Serv IS NULL OR Arv2Serv=0)) OR (Arv3Prsc<>"" AND (Arv3Serv IS NULL OR Arv3Serv=0))) AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND Doss.RensSexe=2 AND Doss.RensAge_<15 nb_rupt_fm<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND ((Arv0Prsc<>"" AND (Arv0Serv IS NULL OR Arv0Serv=0)) OR (Arv1Prsc<>"" AND (Arv1Serv IS NULL OR Arv1Serv=0)) OR (Arv2Prsc<>"" AND (Arv2Serv IS NULL OR Arv2Serv=0)) OR (Arv3Prsc<>"" AND (Arv3Serv IS NULL OR Arv3Serv=0))) AND ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND Doss.RensSexe=2 AND Doss.RensAge_>=15 SET @nb_jour=180 DROP TABLE IF EXISTS Temp CREATE TEMPORARY TABLE Temp (Doss INT,Sexe INT,Age_ INT,DebuArv_ DATETIME) INSERT INTO Temp (Doss,Sexe,Age_,DebuArv_) SELECT DISTINCT MediCons.Doss,Doss.RensSexe,Doss.RensAge_,MIN(MediCons.Dat_) FROM Doss,MediCons WHERE Doss.Nume=MediCons.Doss AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND Doss.Arv_Desi<>"" AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) GROUP BY MediCons.Doss; DROP TABLE IF EXISTS Temp2 CREATE TEMPORARY TABLE Temp2 (Doss INT,Fin_Io__ DATETIME) INSERT INTO Temp2 (Doss,Fin_Io__) SELECT DISTINCT Doss,MAX(Dat_) FROM MediCons WHERE ( Dat_ BETWEEN debut_periode AND fin_periode ) AND ConcCase>0 AND SUBSTR(ConcCase,1,1)<>1 GROUP BY Doss nb_arv6_io2_hm_enf<-SELECT COUNT( DISTINCT Temp.Doss ) FROM Temp,Temp2 WHERE Temp.Doss=Temp2.Doss AND DATEDIFF(Temp2.Fin_Io__,Temp.DebuArv_)>@nb_jour AND Temp.Sexe=1 AND Temp.Age_<15 nb_arv6_io2_hm<-SELECT COUNT( DISTINCT Temp.Doss ) FROM Temp,Temp2 WHERE Temp.Doss=Temp2.Doss AND DATEDIFF(Temp2.Fin_Io__,Temp.DebuArv_)>@nb_jour AND Temp.Sexe=1 AND Temp.Age_>=15 nb_arv6_io2_fm_enf<-SELECT COUNT( DISTINCT Temp.Doss ) FROM Temp,Temp2 WHERE Temp.Doss=Temp2.Doss AND DATEDIFF(Temp2.Fin_Io__,Temp.DebuArv_)>@nb_jour AND Temp.Sexe=2 AND Temp.Age_<15 nb_arv6_io2_fm<-SELECT COUNT( DISTINCT Temp.Doss ) FROM Temp,Temp2 WHERE Temp.Doss=Temp2.Doss AND DATEDIFF(Temp2.Fin_Io__,Temp.DebuArv_)>@nb_jour AND Temp.Sexe=2 AND Temp.Age_>=15 nb_doss_vih<-SELECT COUNT(DISTINCT Doss.Nume) FROM Doss WHERE OuvrDat_<= fin_periode AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND MediSero=1 set @cotrim=(SELECT Nume FROM Dci_ WHERE Desi="sulfaméthoxazole + triméthoprime") nb_doss_cotrim<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND Doss.Nume=MediCons.Doss AND (Med0Dci_=@cotrim OR Med1Dci_=@cotrim OR Med2Dci_=@cotrim OR Med3Dci_=@cotrim OR Med4Dci_=@cotrim OR Med5Dci_=@cotrim) AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) nb_doss_rupt_cotrim<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND Doss.Nume=MediCons.Doss AND ((Med0Dci_=@cotrim AND (Med0Serv IS NULL OR Med0Serv=0)) OR (Med1Dci_=@cotrim AND (Med1Serv IS NULL OR Med1Serv=0)) OR (Med2Dci_=@cotrim AND (Med2Serv IS NULL OR Med2Serv=0)) OR (Med3Dci_=@cotrim AND (Med3Serv IS NULL OR Med3Serv=0)) OR (Med4Dci_=@cotrim AND (Med4Serv IS NULL OR Med4Serv=0)) OR (Med5Dci_=@cotrim AND (Med5Serv IS NULL OR Med5Serv=0))) nb_doss_vih_tb<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND Doss.Nume=MediCons.Doss AND (SUBSTR(ConcCase,14,1)=1 OR SUBSTR(ConcCase,27,1)=1) AND Doss.MediSero=1 set @tb1=(SELECT Nume FROM Dci_ WHERE Desi="rifampicine") set @tb2=(SELECT Nume FROM Dci_ WHERE Desi="éthambutol") nb_doss_arv_antitb<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND Doss.Nume=MediCons.Doss AND Doss.Arv_Desi<>"" AND (Med0Dci_=@tb1 OR Med1Dci_=@tb1 OR Med2Dci_=@tb1 OR Med3Dci_=@tb1 OR Med4Dci_=@tb1 OR Med5Dci_=@tb1 OR Med0Dci_=@tb2 OR Med1Dci_=@tb2 OR Med2Dci_=@tb2 OR Med3Dci_=@tb2 OR Med4Dci_=@tb2 OR Med5Dci_=@tb2) nb_doss_gros<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,PtmeGros WHERE (PtmeGros.SaisDat_ BETWEEN debut_periode AND fin_periode ) AND Doss.Nume=PtmeGros.Doss AND Doss.MediSero=1 nb_doss_gros_prot<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons,PtmeGros WHERE (PtmeGros.SaisDat_ BETWEEN debut_periode AND fin_periode ) AND Doss.Nume=PtmeGros.Doss AND (MediCons.Arv0Prsc<>"" OR MediCons.Arv1Prsc<>"" OR MediCons.Arv2Prsc<>"" OR MediCons.Arv3Prsc<>"") AND (MediCons.Dat_<= fin_periode ) AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Doss.MediSero=1 set @prot="AZT+NVP" nb_doss_gros_prot1<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,PtmeGros WHERE (PtmeGros.SaisDat_ BETWEEN debut_periode AND fin_periode ) AND Doss.Nume=PtmeGros.Doss AND Doss.MediSero=1 AND PtmeGros.Arv_Trai=@prot SET @prot="Trithérapie" nb_doss_gros_prot2<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,PtmeGros WHERE (PtmeGros.SaisDat_ BETWEEN debut_periode AND fin_periode ) AND Doss.Nume=PtmeGros.Doss AND Doss.MediSero=1 AND PtmeGros.Arv_Trai=@prot nb_enf_vih <-SELECT COUNT(DISTINCT Doss.Nume) FROM Doss WHERE ( OuvrDat_ BETWEEN debut_periode AND fin_periode ) AND MediSero=1 AND (Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Doss.RensAge_<=5 nb_enf_elig_arv<-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons,MediConsCond WHERE ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND Doss.Nume=MediCons.Doss AND MediCons.Cond=MediConsCond.Nume AND MediConsCond.Desi="A mettre sous ARV" AND Doss.Arv_Desi="" AND (Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Doss.RensAge_<=5 nb_enf_arv <-SELECT COUNT(*) FROM Doss WHERE ( OuvrDat_ BETWEEN debut_periode AND fin_periode ) AND Arv_Desi<>"" AND RensDeceDat_=0 AND ( Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND RensAge_<=5 set @cotrim=(SELECT Nume FROM Dci_ WHERE Desi="sulfaméthoxazole + triméthoprime") nb_enf_cotrim <-SELECT COUNT( DISTINCT Doss.Nume ) FROM Doss,MediCons WHERE ( MediCons.Dat_ BETWEEN debut_periode AND fin_periode ) AND Doss.Nume=MediCons.Doss AND (Med0Dci_=@cotrim OR Med1Dci_=@cotrim OR Med2Dci_=@cotrim OR Med3Dci_=@cotrim OR Med4Dci_=@cotrim OR Med5Dci_=@cotrim) AND (Doss.RensDeceDat_=0 OR Doss.RensDeceDat_> fin_periode ) AND Doss.RensAge_<=5 nb_doss_piqu<-SELECT COUNT(*) FROM Doss WHERE ( OuvrDat_ BETWEEN debut_periode AND fin_periode ) AND MediRisqPiqu=1 nb_doss_piqu_arv<-SELECT COUNT(*) FROM Doss WHERE ( OuvrDat_ BETWEEN debut_periode AND fin_periode ) AND MediRisqPiqu=1 AND Arv_Desi<>"" nb_doss_etp<-SELECT COUNT( DISTINCT Doss ) FROM ObseCons WHERE Dat_ BETWEEN debut_periode AND fin_periode nb_obse<-SELECT COUNT( DISTINCT Doss ) FROM MediCons WHERE Obse<>"" AND Dat_ BETWEEN debut_periode AND fin_periode nb_doss_parole<-SELECT COUNT( DISTINCT CommDoss.Doss ) FROM Comm,CommDoss WHERE Comm.Nume=CommDoss.Comm AND Acti="Groupe de parole" AND Dat_ BETWEEN debut_periode AND fin_periode #### affichage #### Période|debut_periode|fin_periode -- Volet Dépistage --|Hommes |Femmes |Total Nombre de personnes ayant bénéficié d'un conseil pré test|nb_conseils_hm| nb_conseils_fm| nb_conseils Nombre de personnes ayant effectué le test de dépistage|nb_tests_hm| nb_tests_fm| nb_tests Nombre de personnes ayant récupéré les résultats| nb_retraits_hm| nb_retraits_fm| nb_retraits Nombre moyen de CD4 réalisé par mois| nb_cd4_mois_hm| nb_cd4_mois_fm| nb_cd4_mois -- Traitement antirétroviral --|H<15ans |H>=15ans |F<15ans |F>=15ans| Nombre de patients éligibles au traitement antirétroviral|nb_doss_elig_arv_hm_enf|nb_doss_elig_arv_hm|nb_doss_elig_arv_fm_enf|nb_doss_elig_arv_fm Nombre de patients sous traitement antirétroviral|nb_arv_hm_enf|nb_arv_hm|nb_arv_fm_enf|nb_arv_fm Nombre de patients sous ARV en 1ère ligne|nb_doss_arv1_hm_enf|nb_doss_arv1_hm|nb_doss_arv1_fm_enf|nb_doss_arv1_fm Nombre de patients sous ARV en 2ème ligne|nb_doss_arv2_hm_enf|nb_doss_arv2_hm|nb_doss_arv2_fm_enf|nb_doss_arv2_fm Nombre de patients sous ARV perdus de vue|nb_arv_pdv_hm_enf|nb_arv_pdv_hm|nb_arv_pdv_fm_enf|nb_arv_pdv_fm Nbre de patients toujours en vie 6 mois après le début des ARV|nb_surv6_hm_enf|nb_surv6_hm|nb_surv6_fm_enf|nb_surv6_fm Nbre de patients toujours en vie 12 mois après le début des ARV|nb_surv12_hm_enf|nb_surv12_hm|nb_surv12_fm_enf|nb_surv12_fm Nbre de patients toujours en vie 24 mois après le début des ARV|nb_surv24_hm_enf|nb_surv24_hm|nb_surv24_fm_enf|nb_surv24_fm Nombre de patients dont une prescription d'ARV n'a pas été servie|nb_rupt_hm_enf|nb_rupt_hm|nb_rupt_fm_enf|nb_rupt_fm Nombre de patients sous ARV depuis au moins 6 mois qui font une infection opportuniste|nb_arv6_io2_hm_enf|nb_arv6_io2_hm|nb_arv6_io2_fm_enf|nb_arv6_io2_fm -- Chimioprophylaxie au cotrimoxazole --|Nombre Nombre de patients éligibles au cotrimoxazole|nb_doss_vih Nombre de patients sous cotrimoxazole|nb_doss_cotrim Nombre de patients ayant subi une rupture de stock de cotrimoxazole|nb_doss_rupt_cotrim -- Co-infection VIH/TB --|Nombre Nombre de patients présentant une co-infectionVIH/TB|nb_doss_vih_tb Nombre de patients présentant une co-infectionVIH/TB recevant à la fois des ARV et des antituberculeux|nb_doss_arv_antitb -- PTME --|Nombre Nombre de femmes enceintes VIH+ reçues|nb_doss_gros Nombre de femmes enceintes VIH+ recevant un TAR|nb_doss_gros_prot Nombre de femmes enceintes VIH+ sous AZT+NVP dose|nb_doss_gros_prot1 Nombre de femmes enceintes VIH+ sous Trithérapie|nb_doss_gros_prot2 -- Prise en charge pédiatrique des enfants infectés par le VIH --|Nombre Nombre d'enfants VIH+ confirmés suivis dans le centre|nb_enf_vih Nombre d'enfants VIH+ éligibles au TAR|nb_enf_elig_arv Nombre d'enfants VIH+ sous TAR|nb_enf_arv Nombre d'enfants VIH+ sous cotrimoxazole|nb_enf_cotrim -- Accident d'exposition au sang --|Nombre Nombre de patients victimes d'AES|nb_doss_piqu Nombre de patients victimes d'AES ayant reçu un TAR prophylactique|nb_doss_piqu_arv -- Prise en charge psychosociale --|Nombre Nombre de patients bénéficiant de séance d'éducation thérapeutique|nb_doss_etp Nombre de patients bénéficiant de la consultation d'observance|nb_obse Nombre de patients bénéficiant de groupe de parole|nb_doss_parole |