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 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167
| SELECT
T_Agents.ID_Agent,
T_Agents.NOM_Prenom,
IIf(T_Structures!FK_Type_Hierarchique=5,
T_Structures!ID_Structure,
Null) AS Cellule,
IIf(T_Structures!FK_Type_Hierarchique=4,
T_Structures!ID_Structure,
IIf([Cellule] Is Not Null,
IIf(T_Structures_1!FK_Type_Hierarchique=4,
T_Structures_1!ID_Structure,
Null),
Null)) AS Service,
IIf(T_Structures!FK_Type_Hierarchique=3,
T_Structures!ID_Structure,
IIf([Service] Is Not Null
And [Cellule] Is Not Null,
IIf(T_Structures_2!FK_Type_Hierarchique=3,
T_Structures_2!ID_Structure,
Null),
IIf([Service] Is Not Null
And [Cellule] Is Null,
IIf(T_Structures_1!FK_Type_Hierarchique=3,
T_Structures_1!ID_Structure,
Null),
IIf([Service] Is Null
And [Cellule] Is Not Null,
IIf(T_Structures_1!FK_Type_Hierarchique=3,
T_Structures_1!ID_Structure,
Null))))) AS Direction,
IIf([Pole_Cellule_Service_Direction] Is Not Null,
[Pole_Cellule_Service_Direction],
IIf([Pole_Cellule_Service] Is Not Null,
[Pole_Cellule_Service],
IIf([Pole_Cellule_Direction] Is Not Null,
[Pole_Cellule_Direction],
IIf([Pole_Service_Direction] Is Not Null,
[Pole_Service_Direction],
IIf([Pole_Cellule] Is Not Null,
[Pole_Cellule],
IIf([Pole_Service] Is Not Null,
[Pole_Service],
IIf([Pole_Direction] Is Not Null,
[Pole_Direction],
IIf([Pole_Seul] Is Not Null,
[Pole_Seul],
Null)))))))) AS Pole,
IIf([Direction] Is Not Null
And [Service] Is Not Null
And [Cellule] Is Not Null,
IIf(T_Structures_3!FK_Type_Hierarchique=2,
T_Structures_3!ID_Structure,
Null)) AS Pole_Cellule_Service_Direction,
IIf([Direction] Is Null
And [Service] Is Not Null
And [Cellule] Is Not Null,
IIf(T_Structures_2!FK_Type_Hierarchique=2,
T_Structures_2!ID_Structure,
Null)) AS Pole_Cellule_Service,
IIf([Direction] Is Not Null
And [Service] Is Null
And [Cellule] Is Not Null,
IIf(T_Structures_2!FK_Type_Hierarchique=2,
T_Structures_2!ID_Structure,
Null)) AS Pole_Cellule_Direction,
IIf([Direction] Is Not Null
And [Service] Is Not Null
And [Cellule] Is Null,
IIf(T_Structures_2!FK_Type_Hierarchique=2,
T_Structures_2!ID_Structure,
Null)) AS Pole_Service_Direction,
IIf([Direction] Is Null
And [Service] Is Null
And [Cellule] Is Not Null,
IIf(T_Structures_1!FK_Type_Hierarchique=2,
T_Structures_1!ID_Structure,
Null)) AS Pole_Cellule,
IIf([Direction] Is Null
And [Service] Is Not Null
And [Cellule] Is Null,
IIf(T_Structures_1!FK_Type_Hierarchique=2,
T_Structures_1!ID_Structure,
Null)) AS Pole_Service,
IIf([Direction] Is Not Null
And [Service] Is Null
And [Cellule] Is Null,
IIf(T_Structures_1!FK_Type_Hierarchique=2,
T_Structures_1!ID_Structure,
Null)) AS Pole_Direction,
IIf(T_Structures!FK_Type_Hierarchique=2,
T_Structures!ID_Structure,
Null) AS Pole_Seul,
IIf([Date_fin_Agent_Structure] Is Not Null,
[Date_fin_Agent_Structure],
IIf([Date_Fin_Structure] Is Not Null,
[Date_Fin_Structure],
IIf([Date_Fin_lien] Is Not Null,
[Date_Fin_lien],
IIf([Date_Fin_Structure1] Is Not Null,
[Date_Fin_Structure1],
IIf([Date_Fin_lien1] Is Not Null,
[Date_Fin_lien1],
IIf([Date_Fin_Structure2] Is Not Null,
[Date_Fin_Structure2],
IIf([Date_Fin_lien2] Is Not Null,
[Date_Fin_lien2],
IIf([Date_Fin_Structure3] Is Not Null,
[Date_Fin_Structure3],
Null)))))))) AS DF,
TR_Agent_Structure.Date_debut AS Date_debut_Agent_Structure,
TR_Agent_Structure.Date_fin AS Date_fin_Agent_Structure,
T_Structures.Date_Debut AS Date_Debut_Structure,
T_Structures.Date_Fin AS Date_Fin_Structure,
TR_Structures.Date_Debut AS Date_Debut_lien,
TR_Structures.Date_Fin AS Date_Fin_lien,
T_Structures_1.Date_Debut AS Date_Debut_Structure1,
T_Structures_1.Date_Fin AS Date_Fin_Structure1,
TR_Structures_1.Date_Debut AS Date_Debut_lien1,
TR_Structures_1.Date_Fin AS Date_Fin_lien1,
T_Structures_2.Date_Debut AS Date_Debut_Structure2,
T_Structures_2.Date_Fin AS Date_Fin_Structure2,
TR_Structures_2.Date_Debut AS Date_Debut_lien2,
TR_Structures_2.Date_Fin AS Date_Fin_lien2,
T_Structures_3.Date_Debut AS Date_Debut_Structure3,
T_Structures_3.Date_Fin AS Date_Fin_Structure3
FROM
T_Agents
LEFT JOIN
(
T_Structures AS T_Structures_3
RIGHT JOIN
(
(
T_Structures AS T_Structures_2
RIGHT JOIN
(
(
(
T_Structures
RIGHT JOIN
TR_Agent_Structure
ON T_Structures.ID_Structure = TR_Agent_Structure.FK_Structure
)
LEFT JOIN
(
T_Structures AS T_Structures_1
RIGHT JOIN
TR_Structures
ON T_Structures_1.ID_Structure = TR_Structures.FK_Structure_pere
)
ON T_Structures.ID_Structure = TR_Structures.FK_Structure_fille
)
LEFT JOIN
TR_Structures AS TR_Structures_1
ON T_Structures_1.ID_Structure = TR_Structures_1.FK_Structure_fille
)
ON T_Structures_2.ID_Structure = TR_Structures_1.FK_Structure_pere
)
LEFT JOIN
TR_Structures AS TR_Structures_2
ON T_Structures_2.ID_Structure = TR_Structures_2.FK_Structure_fille
)
ON T_Structures_3.ID_Structure = TR_Structures_2.FK_Structure_pere
)
ON T_Agents.ID_Agent = TR_Agent_Structure.FK_Agent
ORDER BY
T_Agents.NOM_Prenom; |
Partager