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 168 169 170 171 172 173
|
SELECT top 10 A.STD_ID_HR
,CASE
WHEN A.STD_DT_START>=B.SFR_DT_START AND A.STD_DT_START>=C.SFR_DT_START AND A.STD_DT_START>=D.SFR_DT_START AND A.STD_DT_START>=E.STD_DT_START AND A.STD_DT_START>=F.SFR_DT_START AND A.STD_DT_START>=G.SFR_DT_START AND A.STD_DT_START>=H.SFR_DT_START AND A.STD_DT_START>=I.SFR_DT_START THEN A.STD_DT_START
WHEN B.SFR_DT_START>=A.STD_DT_START AND B.SFR_DT_START>=C.SFR_DT_START AND B.SFR_DT_START>=D.SFR_DT_START AND B.SFR_DT_START>=E.STD_DT_START AND B.SFR_DT_START>=F.SFR_DT_START AND B.SFR_DT_START>=G.SFR_DT_START AND B.SFR_DT_START>=H.SFR_DT_START AND B.SFR_DT_START>=I.SFR_DT_START THEN B.SFR_DT_START
WHEN C.SFR_DT_START>=A.STD_DT_START AND C.SFR_DT_START>=B.SFR_DT_START AND C.SFR_DT_START>=D.SFR_DT_START AND C.SFR_DT_START>=E.STD_DT_START AND C.SFR_DT_START>=F.SFR_DT_START AND C.SFR_DT_START>=G.SFR_DT_START AND C.SFR_DT_START>=H.SFR_DT_START AND C.SFR_DT_START>=I.SFR_DT_START THEN C.SFR_DT_START
WHEN D.SFR_DT_START>=A.STD_DT_START AND D.SFR_DT_START>=B.SFR_DT_START AND D.SFR_DT_START>=C.SFR_DT_START AND D.SFR_DT_START>=E.STD_DT_START AND D.SFR_DT_START>=F.SFR_DT_START AND D.SFR_DT_START>=G.SFR_DT_START AND D.SFR_DT_START>=H.SFR_DT_START AND D.SFR_DT_START>=I.SFR_DT_START THEN D.SFR_DT_START
WHEN E.STD_DT_START>=A.STD_DT_START AND E.STD_DT_START>=B.SFR_DT_START AND E.STD_DT_START>=C.SFR_DT_START AND E.STD_DT_START>=D.SFR_DT_START AND E.STD_DT_START>=F.SFR_DT_START AND E.STD_DT_START>=G.SFR_DT_START AND E.STD_DT_START>=H.SFR_DT_START AND E.STD_DT_START>=I.SFR_DT_START THEN E.STD_DT_START
WHEN F.SFR_DT_START>=A.STD_DT_START AND F.SFR_DT_START>=B.SFR_DT_START AND F.SFR_DT_START>=C.SFR_DT_START AND F.SFR_DT_START>=D.SFR_DT_START AND F.SFR_DT_START>=E.STD_DT_START AND F.SFR_DT_START>=G.SFR_DT_START AND F.SFR_DT_START>=H.SFR_DT_START AND F.SFR_DT_START>=I.SFR_DT_START THEN F.SFR_DT_START
WHEN G.SFR_DT_START>=A.STD_DT_START AND G.SFR_DT_START>=B.SFR_DT_START AND G.SFR_DT_START>=C.SFR_DT_START AND G.SFR_DT_START>=D.SFR_DT_START AND G.SFR_DT_START>=E.STD_DT_START AND G.SFR_DT_START>=F.SFR_DT_START AND G.SFR_DT_START>=H.SFR_DT_START AND G.SFR_DT_START>=I.SFR_DT_START THEN G.SFR_DT_START
WHEN H.SFR_DT_START>=A.STD_DT_START AND H.SFR_DT_START>=B.SFR_DT_START AND H.SFR_DT_START>=C.SFR_DT_START AND H.SFR_DT_START>=D.SFR_DT_START AND H.SFR_DT_START>=E.STD_DT_START AND H.SFR_DT_START>=F.SFR_DT_START AND H.SFR_DT_START>=G.SFR_DT_START AND H.SFR_DT_START>=I.SFR_DT_START THEN H.SFR_DT_START
WHEN I.SFR_DT_START>=A.STD_DT_START AND I.SFR_DT_START>=B.SFR_DT_START AND I.SFR_DT_START>=C.SFR_DT_START AND I.SFR_DT_START>=D.SFR_DT_START AND I.SFR_DT_START>=E.STD_DT_START AND I.SFR_DT_START>=F.SFR_DT_START AND I.SFR_DT_START>=G.SFR_DT_START AND I.SFR_DT_START>=H.SFR_DT_START THEN I.SFR_DT_START
END
,CASE
WHEN A.STD_DT_END<=B.SFR_DT_END AND A.STD_DT_END<=C.SFR_DT_END AND A.STD_DT_END<=D.SFR_DT_END AND A.STD_DT_END<=E.STD_DT_END AND A.STD_DT_END<=F.SFR_DT_END AND A.STD_DT_END<=G.SFR_DT_END AND A.STD_DT_END<=H.SFR_DT_END AND A.STD_DT_END<=I.SFR_DT_END THEN A.STD_DT_END
WHEN B.SFR_DT_END<=A.STD_DT_END AND B.SFR_DT_END<=C.SFR_DT_END AND B.SFR_DT_END<=D.SFR_DT_END AND B.SFR_DT_END<=E.STD_DT_END AND B.SFR_DT_END<=F.SFR_DT_END AND B.SFR_DT_END<=G.SFR_DT_END AND B.SFR_DT_END<=H.SFR_DT_END AND B.SFR_DT_END<=I.SFR_DT_END THEN B.SFR_DT_END
WHEN C.SFR_DT_END<=A.STD_DT_END AND C.SFR_DT_END<=B.SFR_DT_END AND C.SFR_DT_END<=D.SFR_DT_END AND C.SFR_DT_END<=E.STD_DT_END AND C.SFR_DT_END<=F.SFR_DT_END AND C.SFR_DT_END<=G.SFR_DT_END AND C.SFR_DT_END<=H.SFR_DT_END AND C.SFR_DT_END<=I.SFR_DT_END THEN C.SFR_DT_END
WHEN D.SFR_DT_END<=A.STD_DT_END AND D.SFR_DT_END<=B.SFR_DT_END AND D.SFR_DT_END<=C.SFR_DT_END AND D.SFR_DT_END<=E.STD_DT_END AND D.SFR_DT_END<=F.SFR_DT_END AND D.SFR_DT_END<=G.SFR_DT_END AND D.SFR_DT_END<=H.SFR_DT_END AND D.SFR_DT_END<=I.SFR_DT_END THEN D.SFR_DT_END
WHEN E.STD_DT_END<=A.STD_DT_END AND E.STD_DT_END<=B.SFR_DT_END AND E.STD_DT_END<=C.SFR_DT_END AND E.STD_DT_END<=D.SFR_DT_END AND E.STD_DT_END<=F.SFR_DT_END AND E.STD_DT_END<=G.SFR_DT_END AND E.STD_DT_END<=H.SFR_DT_END AND E.STD_DT_END<=I.SFR_DT_END THEN E.STD_DT_END
WHEN F.SFR_DT_END<=A.STD_DT_END AND F.SFR_DT_END<=B.SFR_DT_END AND F.SFR_DT_END<=C.SFR_DT_END AND F.SFR_DT_END<=D.SFR_DT_END AND F.SFR_DT_END<=E.STD_DT_END AND F.SFR_DT_END<=G.SFR_DT_END AND F.SFR_DT_END<=H.SFR_DT_END AND F.SFR_DT_END<=I.SFR_DT_END THEN F.SFR_DT_END
WHEN G.SFR_DT_END<=A.STD_DT_END AND G.SFR_DT_END<=B.SFR_DT_END AND G.SFR_DT_END<=C.SFR_DT_END AND G.SFR_DT_END<=D.SFR_DT_END AND G.SFR_DT_END<=E.STD_DT_END AND G.SFR_DT_END<=F.SFR_DT_END AND G.SFR_DT_END<=H.SFR_DT_END AND G.SFR_DT_END<=I.SFR_DT_END THEN G.SFR_DT_END
WHEN H.SFR_DT_END<=A.STD_DT_END AND H.SFR_DT_END<=B.SFR_DT_END AND H.SFR_DT_END<=C.SFR_DT_END AND H.SFR_DT_END<=D.SFR_DT_END AND H.SFR_DT_END<=E.STD_DT_END AND H.SFR_DT_END<=F.SFR_DT_END AND H.SFR_DT_END<=G.SFR_DT_END AND H.SFR_DT_END<=I.SFR_DT_END THEN H.SFR_DT_END
WHEN I.SFR_DT_END<=A.STD_DT_END AND I.SFR_DT_END<=B.SFR_DT_END AND I.SFR_DT_END<=C.SFR_DT_END AND I.SFR_DT_END<=D.SFR_DT_END AND I.SFR_DT_END<=E.STD_DT_END AND I.SFR_DT_END<=F.SFR_DT_END AND I.SFR_DT_END<=G.SFR_DT_END AND I.SFR_DT_END<=H.SFR_DT_END THEN I.SFR_DT_END
END
,A1.STD_N_FAM_NAME_1
,A.STD_DT_START
,A.STD_DT_END
,A.STD_OR_HR_PERIOD
,B.SFR_DT_START
,B.SFR_DT_END
,B.SFR_ID_S_LEG_ENT
,B1.SFR_NM_S_LEG_ENT
,B1.SFR_ID_LEG_ENT
,B2.STD_N_LEG_ENT
,C.SFR_DT_START
,C.SFR_DT_END
,C.SFR_ID_LAB_AGREE
,C1.SFR_NM_LAB_AGREE
,D.SFR_DT_START
,D.SFR_DT_END
,D.SFR_ID_CAT_SP
,D1.SFR_NM_CAT_SP
,E.STD_DT_START
,E.STD_DT_END
,E.STD_ID_CONT_TYPE
,E1.STD_N_CONT_TYPE
,F.SFR_DT_START
,F.SFR_DT_END
,F.SFR_ID_CAT_SOC_SEC
,F1.SFR_NM_CAT_SOC_SEC
,G.SFR_DT_START
,G.SFR_DT_END
,G.M4SFR_ID_CAT_RETR
,G1.M4SFR_NM_CAT_RETR
,H.SFR_DT_START
,H.SFR_DT_END
,H.M4SFR_ID_CAT_PREV
,H1.M4SFR_NM_CAT_PREV
,I.SFR_DT_START
,I.SFR_DT_END
,I.SFR_ID_CAT_MUTUELL
,I1.SFR_NM_CAT_MUTUELL
FROM STD_HR_PERIOD A
,STD_PERSON A1
,M4SFR_H_HR_SUB_LE B
,M4SFR_SUB_LEG_ENT B1
,STD_LEG_ENT B2
,M4SFR_H_HR_LAB_AG C
,M4SFR_LAB_AGREE C1
,M4SFR_H_HR_CAT_SP D
,M4SFR_X_CAT_SOC_PR D1
,STD_INT_WORK_CONT E
,STD_CONTRACT_TYPE E1
,M4SFR_H_HR_CAT_SS F
,M4SFR_X_CAT_SS F1
,M4SFR_H_HR_CAT_RET G
,M4SFR_X_CAT_RETR G1
,M4SFR_H_HR_CAT_PRE H
,M4SFR_X_CAT_PREVOY H1
,M4SFR_H_HR_CAT_MUT I
,M4SFR_X_CAT_MUTUEL I1
WHERE A.STD_ID_HR = A1.STD_ID_PERSON
/* Etablissement, Société */
AND A.STD_ID_HR = B.SCO_ID_HR
AND A.STD_OR_HR_PERIOD = B.SCO_OR_HR_PERIOD
AND B.SFR_DT_START <= C.SFR_DT_END AND B.SFR_DT_END >= C.SFR_DT_START
AND B.SFR_DT_START <= D.SFR_DT_END AND B.SFR_DT_END >= D.SFR_DT_START
AND B.SFR_DT_START <= E.STD_DT_END AND B.SFR_DT_END >= E.STD_DT_START
AND B.SFR_DT_START <= F.SFR_DT_END AND B.SFR_DT_END >= F.SFR_DT_START
AND B.SFR_DT_START <= G.SFR_DT_END AND B.SFR_DT_END >= G.SFR_DT_START
AND B.SFR_DT_START <= H.SFR_DT_END AND B.SFR_DT_END >= H.SFR_DT_START
AND B.SFR_DT_START <= I.SFR_DT_END AND B.SFR_DT_END >= I.SFR_DT_START
AND B.SFR_ID_S_LEG_ENT = B1.SFR_ID_S_LEG_ENT
AND B1.SFR_ID_LEG_ENT = B2.STD_ID_LEG_ENT
/* Conv. Collective */
AND A.STD_ID_HR = C.SFR_ID_HR
AND A.STD_OR_HR_PERIOD = C.SFR_OR_HR_PERIOD
AND C.SFR_DT_START <= B.SFR_DT_END AND C.SFR_DT_END >= B.SFR_DT_START
AND C.SFR_DT_START <= D.SFR_DT_END AND C.SFR_DT_END >= D.SFR_DT_START
AND C.SFR_DT_START <= E.STD_DT_END AND C.SFR_DT_END >= E.STD_DT_START
AND C.SFR_DT_START <= F.SFR_DT_END AND C.SFR_DT_END >= F.SFR_DT_START
AND C.SFR_DT_START <= G.SFR_DT_END AND C.SFR_DT_END >= G.SFR_DT_START
AND C.SFR_DT_START <= H.SFR_DT_END AND C.SFR_DT_END >= H.SFR_DT_START
AND C.SFR_DT_START <= I.SFR_DT_END AND C.SFR_DT_END >= I.SFR_DT_START
AND C.SFR_ID_LAB_AGREE = C1.SFR_ID_LAB_AGREE
/* Catégorie Socio-Pro */
AND A.STD_ID_HR = D.SFR_ID_HR
AND A.STD_OR_HR_PERIOD = D.SFR_OR_HR_PERIOD
AND D.SFR_DT_START <= B.SFR_DT_END AND D.SFR_DT_END >= B.SFR_DT_START
AND D.SFR_DT_START <= C.SFR_DT_END AND D.SFR_DT_END >= C.SFR_DT_START
AND D.SFR_DT_START <= E.STD_DT_END AND D.SFR_DT_END >= E.STD_DT_START
AND D.SFR_DT_START <= F.SFR_DT_END AND D.SFR_DT_END >= F.SFR_DT_START
AND D.SFR_DT_START <= G.SFR_DT_END AND D.SFR_DT_END >= G.SFR_DT_START
AND D.SFR_DT_START <= H.SFR_DT_END AND D.SFR_DT_END >= H.SFR_DT_START
AND D.SFR_DT_START <= I.SFR_DT_END AND D.SFR_DT_END >= I.SFR_DT_START
AND D.SFR_ID_CAT_SP = D1.SFR_ID_CAT_SP
/* Contrat Interne */
AND A.STD_ID_HR = E.STD_ID_HR
AND A.STD_OR_HR_PERIOD = E.STD_OR_HR_PERIOD
AND E.STD_DT_START <= B.SFR_DT_END AND E.STD_DT_END >= B.SFR_DT_START
AND E.STD_DT_START <= C.SFR_DT_END AND E.STD_DT_END >= C.SFR_DT_START
AND E.STD_DT_START <= D.SFR_DT_END AND E.STD_DT_END >= D.SFR_DT_START
AND E.STD_DT_START <= F.SFR_DT_END AND E.STD_DT_END >= F.SFR_DT_START
AND E.STD_DT_START <= G.SFR_DT_END AND E.STD_DT_END >= G.SFR_DT_START
AND E.STD_DT_START <= H.SFR_DT_END AND E.STD_DT_END >= H.SFR_DT_START
AND E.STD_DT_START <= I.SFR_DT_END AND E.STD_DT_END >= I.SFR_DT_START
AND E.STD_ID_CONT_TYPE = E1.STD_ID_CONT_TYPE
/* Cat Sécurité Sociale */
AND A.STD_ID_HR = F.SFR_ID_HR
AND A.STD_OR_HR_PERIOD = F.SFR_OR_HR_PERIOD
AND F.SFR_DT_START <= B.SFR_DT_END AND F.SFR_DT_END >= B.SFR_DT_START
AND F.SFR_DT_START <= C.SFR_DT_END AND F.SFR_DT_END >= C.SFR_DT_START
AND F.SFR_DT_START <= D.SFR_DT_END AND F.SFR_DT_END >= D.SFR_DT_START
AND F.SFR_DT_START <= E.STD_DT_END AND F.SFR_DT_END >= E.STD_DT_START
AND F.SFR_DT_START <= G.SFR_DT_END AND F.SFR_DT_END >= G.SFR_DT_START
AND F.SFR_DT_START <= H.SFR_DT_END AND F.SFR_DT_END >= H.SFR_DT_START
AND F.SFR_DT_START <= I.SFR_DT_END AND F.SFR_DT_END >= I.SFR_DT_START
AND F.SFR_ID_CAT_SOC_SEC = F1.SFR_ID_CAT_SOC_SEC
/* Cat Retraite */
AND A.STD_ID_HR = G.STD_ID_HR
AND A.STD_OR_HR_PERIOD = G.STD_OR_HR_PERIOD
AND G.SFR_DT_START <= B.SFR_DT_END AND G.SFR_DT_END >= B.SFR_DT_START
AND G.SFR_DT_START <= C.SFR_DT_END AND G.SFR_DT_END >= C.SFR_DT_START
AND G.SFR_DT_START <= D.SFR_DT_END AND G.SFR_DT_END >= D.SFR_DT_START
AND G.SFR_DT_START <= E.STD_DT_END AND G.SFR_DT_END >= E.STD_DT_START
AND G.SFR_DT_START <= F.SFR_DT_END AND G.SFR_DT_END >= F.SFR_DT_START
AND G.SFR_DT_START <= H.SFR_DT_END AND G.SFR_DT_END >= H.SFR_DT_START
AND G.SFR_DT_START <= I.SFR_DT_END AND G.SFR_DT_END >= I.SFR_DT_START
AND G.M4SFR_ID_CAT_RETR = G1.M4SFR_ID_CAT_RETR
/* Cat Prévoyance */
AND A.STD_ID_HR = H.STD_ID_HR
AND A.STD_OR_HR_PERIOD = H.STD_OR_HR_PERIOD
AND H.SFR_DT_START <= B.SFR_DT_END AND H.SFR_DT_END >= B.SFR_DT_START
AND H.SFR_DT_START <= C.SFR_DT_END AND H.SFR_DT_END >= C.SFR_DT_START
AND H.SFR_DT_START <= D.SFR_DT_END AND H.SFR_DT_END >= D.SFR_DT_START
AND H.SFR_DT_START <= E.STD_DT_END AND H.SFR_DT_END >= E.STD_DT_START
AND H.SFR_DT_START <= F.SFR_DT_END AND H.SFR_DT_END >= F.SFR_DT_START
AND H.SFR_DT_START <= G.SFR_DT_END AND H.SFR_DT_END >= G.SFR_DT_START
AND H.SFR_DT_START <= I.SFR_DT_END AND H.SFR_DT_END >= I.SFR_DT_START
AND H.M4SFR_ID_CAT_PREV = H1.M4SFR_ID_CAT_PREV
/* Cat Mutuelle */
AND A.STD_ID_HR = I.STD_ID_HR
AND A.STD_OR_HR_PERIOD = I.STD_OR_HR_PERIOD
AND I.SFR_DT_START <= B.SFR_DT_END AND I.SFR_DT_END >= B.SFR_DT_START
AND I.SFR_DT_START <= C.SFR_DT_END AND I.SFR_DT_END >= C.SFR_DT_START
AND I.SFR_DT_START <= D.SFR_DT_END AND I.SFR_DT_END >= D.SFR_DT_START
AND I.SFR_DT_START <= E.STD_DT_END AND I.SFR_DT_END >= E.STD_DT_START
AND I.SFR_DT_START <= F.SFR_DT_END AND I.SFR_DT_END >= F.SFR_DT_START
AND I.SFR_DT_START <= G.SFR_DT_END AND I.SFR_DT_END >= G.SFR_DT_START
AND I.SFR_DT_START <= H.SFR_DT_END AND I.SFR_DT_END >= H.SFR_DT_START
AND I.SFR_ID_CAT_MUTUELL = I1.SFR_ID_CAT_MUTUELL
/* Critères */
AND A.STD_ID_HR_TYPE = '01' |
Partager