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 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
| SELECT
COUNT(Carte.identifiant) AS Comptage_1,
Agent.matricule AS matricule,
Agent.civilite AS civilite,
Agent.nom AS nom,
Agent.prénom AS prénom,
Agent.dateNaissance AS dateNaissance,
Agent.numTel AS numTel,
Agent.Adresseemail AS Adresseemail,
Agent.codeADELI AS codeADELI,
Agent.codeRPPS AS codeRPPS,
TypeCarte.libelle AS libelle,
Grade.libelle AS libelle_Gr,
Métier.libelle AS libelle_Mé,
Pole.libelle AS libelle_Po,
Service.libelle AS libelle_Se,
Spécialité.libelle AS libelle_Sp,
UF.libelle AS libelle_UF,
Carte.codeFINESS AS codeFINESS,
CASE WHEN Suivi.IDOpération = '6' THEN 0 ELSE 1 END AS EtatCarte,
SUM(Suivi.IDOpération) AS la_somme_IDOpération
FROM
Spécialité RIGHT OUTER JOIN Agent ON Agent.IDSpécialité = Spécialité.IDSpécialité,
Pole RIGHT OUTER JOIN Agent ON Agent.IDPole = Pole.IDPole,
Service RIGHT OUTER JOIN Agent ON Agent.IDService = Service.IDService,
Grade RIGHT OUTER JOIN Agent ON Agent.IDGrade = Grade.IDGrade,
Métier RIGHT OUTER JOIN Agent ON Agent.IDMétier = Métier.IDMétier,
UF RIGHT OUTER JOIN Agent ON Agent.IDUF = UF.IDUF,
Agent INNER JOIN Affectation ON Affectation.IDAgent = Agent.IDAgent,
Carte INNER JOIN Affectation ON Affectation.IDCarte = Carte.IDCarte,
Carte LEFT OUTER JOIN Suivi ON Suivi.IDCarte = Carte.IDCarte,
Commande INNER JOIN Carte ON Carte.IDCommande = Commande.IDCommande,
TypeCarte INNER JOIN Commande ON Commande.IDTypeCarte = TypeCarte.IDTypeCarte
WHERE
(
Commande.IDCommande = {Param1}
)
GROUP BY
Agent.matricule,
Agent.civilite,
Agent.nom,
Agent.prénom,
Agent.dateNaissance,
Agent.numTel,
Agent.Adresseemail,
Agent.codeADELI,
Agent.codeRPPS,
TypeCarte.libelle,
Grade.libelle,
Métier.libelle,
Pole.libelle,
Service.libelle,
Spécialité.libelle,
UF.libelle,
Carte.codeFINESS,
CASE WHEN Suivi.IDOpération = '6' THEN 0 ELSE 1 END
HAVING
COUNT(Carte.identifiant) > 1
OR
(
SUM(Suivi.IDOpération) = 6
AND COUNT(Carte.identifiant) = 1
)
ORDER BY
nom ASC,
Comptage_1 ASC |
Partager