1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| SELECT P.NOM_PERSONNEL+' '+P.PRENOM_PERSONNEL
,C.NOM_CENTRE
,PA.LIBELLE_COURT
,CL.DH_DEBUT_SPEC AS 'DH_DEBUT_PLANNING'
,CL.DH_FIN_SPEC AS 'DH_FIN_PLANNING'
,CASE
WHEN CAST(DATEDIFF(mi,CL.DH_FIN_SPEC,CL.DH_DEBUT_SPEC)-60*DATEDIFF(hh,CL.DH_FIN_SPEC,CL.DH_DEBUT_SPEC) as varchar)<=-1 THEN CAST(DATEDIFF(mi,CL.DH_DEBUT_SPEC,CL.DH_FIN_SPEC)/60 as varchar)+':'+CAST(DATEDIFF(mi,CL.DH_DEBUT_SPEC,CL.DH_FIN_SPEC)-60*DATEDIFF(hh,CL.DH_DEBUT_SPEC,CL.DH_FIN_SPEC) as varchar)
ELSE CAST(DATEDIFF(mi,CL.DH_DEBUT_SPEC,CL.DH_FIN_SPEC)/60 as varchar)+':'+CAST(DATEDIFF(mi,CL.DH_FIN_SPEC,CL.DH_DEBUT_SPEC)-60*DATEDIFF(hh,CL.DH_FIN_SPEC,CL.DH_DEBUT_SPEC) as varchar)
END AS 'HEURE_DUREE_PLANNING'
,(CL.DH_DEBUT_SPEC, CL.DH_FIN_SPEC) OVERLAPS (CL.DH_DEBUT_SPEC,CL.DH_FIN_SPEC) FROM PERSONNEL AS P
INNER JOIN SPECIALISTE AS SP ON P.ID_ANNUAIRE_OPE=SP.ID_ANNUAIRE_OPE
INNER JOIN CENTRE AS C ON P.NUMERO_CENTRE=C.NUMERO_CENTRE
INNER JOIN PLANNINGS_CALENDRIER_SPEC AS CL ON P.MATRICULE=CL.MATRICULE
INNER JOIN SPECIALITE AS S ON CL.CODE_FCTOCCUPEE=S.NOM_SPECIALITE
INNER JOIN PLANNINGS_ETAT AS PA ON CL.ETAT=PA.CODE
WHERE CL.DH_DEBUT_SPEC > '01/01/2013 00:00:00'
and PA.LIBELLE_COURT = 'DPV'
or PA.LIBELLE_COURT = 'ACU'
ORDER BY CL.DH_DEBUT_SPEC |
Partager