1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
|
SELECT DISTINCT numero_ent AS Matricule,
FROM Tablepaiement3 t1
INNER JOIN archive2 t2 ON t1.idrefT3 = t2.idrefT3
INNER JOIN archive1 t3 ON t2.idrefT3 = t3.id
INNER JOIN entreprise t4 ON t3.idrp=t4.id
INNER JOIN personne t5 ON t4.idrefT5=t5.idrefT5
INNER JOIN campagneExercice1 t6 ON t3.idreft6 = t6.id
INNER JOIN tablePaiement2 t7 ON t1.idrefT7= t7.id
INNER JOIN entreprise t8 ON t8.id=t2.c211_idEnt
INNER JOIN tableRelationEnt t9 ON t9.idrp_1 =t8.id AND t9.idrp_2 =t4.id
AND t7.dateexercice >= to_date('$anneeRecherche/01/01','yyyy/mm/dd')
AND t7.dateexercice <= to_date('$anneeRecherche/12/31','yyyy/mm/dd')
and t9.date_JUSQUAU <> to_date('2999/12/31','yyyy/mm/dd') --rajout ici ,2999/12/31 veut dire que la personne se trouve toujours dans l'entreprise 1234 aujourd'hui
--je n'ai pas rajouté la condtion de l'entreprise ici
AND t2.statutpersonne IN ('ETAM' ,'ETAMA','ETAMB','IAC','IAC1','IAC2' ,'ING')
and EXISTS
(
SELECT DISTINCT numero_ent AS Matricule,
FROM Tablepaiement3 t1
INNER JOIN archive2 t2 ON t1.idrefT3 = t2.idrefT3
INNER JOIN archive1 t3 ON t2.idrefT3 = t3.id
INNER JOIN entreprise t4 ON t3.idrp=t4.id
INNER JOIN personne t5 ON t4.idrefT5=t5.idrefT5
INNER JOIN campagneExercice1 t6 ON t3.idreft6 = t6.id
INNER JOIN tablePaiement2 t7 ON t1.idrefT7= t7.id
INNER JOIN entreprise t8 ON t8.id=t2.c211_idEnt
INNER JOIN tableRelationEnt t9 ON t9.idrp_1 =t8.id AND t9.idrp_2 =t4.id
WHERE t8.x503_matricule = '$NumEntreprise'
AND t7.dateexercice >= to_date('$anneeRecherche/01/01','yyyy/mm/dd')
AND t7.dateexercice <= to_date('$anneeRecherche/12/31','yyyy/mm/dd')
AND t7.dateexercice <= t9.Date_JUSQUAU
AND t9.date_JUSQUAU>= to_date('$anneeRecherche/12/31','yyyy/mm/dd')
AND t2.statutpersonne IN ('ETAM' ,'ETAMA','ETAMB','IAC','IAC1','IAC2' ,'ING')
) |
Partager