1 2 3 4 5 6 7 8 9 10
| SELECT a.ID_CALENDAR,
MATRICULE,
CASE WHEN LB_WEEK_DAY NOT IN ('Samedi','Dimanche') THEN 1 ELSE 0 END AS JOURS_OUVRABLE,
CASE WHEN DATEDIFF(DAY,CONVERT(char(8),CONVERT(int,(LAG(a.ID_CALENDAR) OVER (PARTITION BY MATRICULE ORDER BY a.ID_CALENDAR))),112),CONVERT(char(8),CONVERT(int,a.ID_CALENDAR),112)) !=1
OR DATEDIFF(DAY,CONVERT(char(8),CONVERT(int,(LAG(a.ID_CALENDAR) OVER (PARTITION BY MATRICULE ORDER BY a.ID_CALENDAR))),112),CONVERT(char(8),CONVERT(int,a.ID_CALENDAR),112)) is null
THEN 1 ELSE 0 END AS DEBUT_ARRET_MALADIE
FROM DIM_ABSENCE a
LEFT OUTER JOIN DIM_CALENDAR b on a.ID_CALENDAR = b.ID_CALENDAR
WHERE ABSENCE_TYPE = 'MALA'
ORDER BY MATRICULE,ID_CALENDAR |
Partager