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
|
SELECT DISTINCT concessions.nom as nomconcessions,
rhp2.nom as nom, rhp2.prenom as prenom,
rhpn.nom_nationalite as nationalite,
rhp2.adresse as adresse,
rhp2.cp as cp,
rhp2.ville as ville,
DATE_FORMAT(date_entree,'%d/%m/%Y')as`date_entree`,
DATE_FORMAT(date_sortie,'%d/%m/%Y')as`date_sortie`,
DATE_FORMAT(date_naissance,'%d/%m/%Y')as`date`,`sexe`,
(Select min(date_debut)
FROM `rh_personnel_suivi` rhps
INNER JOIN rh_personnel rhp ON rhp.id_rhp = rhps.id_rhp
WHERE
rhp.id_rhp=rhp2.id_rhp
) as tttt,(Select max(if(date_fin='0000-00-00','2099-12-31',date_fin))
FROM `rh_personnel_suivi` rhps
INNER JOIN rh_personnel rhp ON rhp.id_rhp = rhps.id_rhp
WHERE
rhp.id_rhp=rhp2.id_rhp)
as tttt2,
nom_emploi as emploi,
CONCAT(nom_classification,'-', nom_echelon) as classification,
nom_contrat as type,
date_vm,
rhps.id_concessions
FROM `rh_personnel_suivi` rhps
INNER JOIN concessions on concessions.id_concessions=rhps.id_concessions
INNER JOIN rh_personnel rhp2 ON rhp2.id_rhp = rhps.id_rhp
INNER JOIN rh_personnel_nationalite rhpn ON rhpn.id_rhpn=rhp2.id_rhpn
INNER JOIN rh_personnel_classification rhpc ON rhpc.id_rhpc = rhps.id_rhpc
INNER JOIN rh_personnel_echelon rhpe ON rhpe.id_rhpe = rhps.id_rhpe
INNER JOIN rh_personnel_type_suivi rhpts ON rhpts.id_rhpts = rhps.id_suivi
INNER JOIN rh_personnel_type_emploi rhpte ON rhpte.id_rhpte = rhps.id_rhpte
INNER JOIN rh_personnel_type_contrat_travail rhptct ON rhptct.id_rhptct = rhps.id_rhptct
WHERE rhps.id_concessions IN ('1')
ORDER BY concessions.nom, rhp2.nom, rhps.id_rhps |
Partager