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
| SELECT CAST(
CASE CHARINDEX('PROD ENTRAIDE', A.HORT_LEGENDE)
WHEN 0 THEN F.SEC_NO_EQUIPE
ELSE SUBSTRING(A.HORT_LEGENDE, CHARINDEX('PROD ENTRAIDE', A.HORT_LEGENDE) + 13, 7)
END AS VARCHAR) + '|' + CAST(B.REP_CODE_TELEPHONIQUE AS VARCHAR) + '|' + CAST(B.REP_NOM AS VARCHAR) + '|' + CAST(B.REP_PRENOM AS VARCHAR) + '|' + CAST(CONVERT(VARCHAR(23), A.HORT_DATE) AS VARCHAR) + '|' + CAST(A.HORT_DUREE_ACTIVITES_MIN AS VARCHAR) + '|' + CAST(
CASE CHARINDEX('ENTRAIDE', A.HORT_LEGENDE)
WHEN 21 THEN ((SUBSTRING(A.HORT_LEGENDE, 1, 20) + 'En service ') + SUBSTRING(A.HORT_LEGENDE, 21, LEN(A.HORT_LEGENDE)))
ELSE A.HORT_LEGENDE
END AS VARCHAR) + '|' + CAST(B.REP_TELEPHONE_01 AS VARCHAR)
FROM CALTYPESCONTRAINTE D RIGHT OUTER JOIN CALCONTRAINTESXPREPOSE E ON D.RES_SEQUENCE = E.REP_RES_XRF_RES_SEQUENCE RIGHT OUTER JOIN CALHORAIRESXJOURXPREPOSE A ON E.REP_RES_SEQUENCE = A.HORT_REF_RES_PRO_SEQUENCE ,
CALPREPOSES B,
CALPREPOSESXEQUIPE C,
CALEQUIPES F
WHERE A.HORT_DATE >= CONVERT(DATETIME, '&1', 103)
AND A.HORT_DATE <= CONVERT(DATETIME, '&2', 103)
AND F.SEC_SEQUENCE = C.REP_SEC_XRF_SEC_SEQUENCE
AND B.REP_SEQUENCE = C.REP_SEC_XRF_REP_SEQUENCE
AND B.REP_SEQUENCE = A.HORT_XRF_REP_SEQUENCE
AND A.HORT_LEGENDE NOT LIKE '%Non disponible%'
AND F.SEC_NO_EQUIPE NOT LIKE '5%'
AND F.SEC_NO_EQUIPE NOT LIKE 'W%'
AND F.SEC_NO_EQUIPE NOT LIKE '0100%'
AND B.REP_TELEPHONE_01 IS NOT NULL
ORDER BY A.HORT_DATE,
C.REP_SEC_REF_NO_EQUIPE,
B.REP_CODE_TELEPHONIQUE,
A.HORT_HEURE_DEBUT |
Partager