1 2 3 4 5 6 7 8 9 10 11 12 13
| WITH
tree (niveau, id, NumSalarie, Commentaire, PeriodeDebut, PeriodeFin)
AS (SELECT 0, id, NumSalarie, Commentaire, PeriodeDebut, PeriodeFin FROM #temp
UNION ALL
SELECT b.niveau + 1, a.id, a.NumSalarie, a.Commentaire, a.PeriodeDebut, b.PeriodeFin FROM #temp a
INNER JOIN tree b
on DATEADD(day, 1, a.PeriodeFin) = b.PeriodeDebut and a.Commentaire = b.Commentaire and a.NumSalarie = b.NumSalarie)
SELECT NumSalarie, Commentaire, min(PeriodeDebut), PeriodeFin
FROM (
SELECT id, NumSalarie, Commentaire, PeriodeDebut, PeriodeFin, ROW_NUMBER() OVER(PARTITION BY id ORDER BY niveau desc) num
FROM tree
) res where num = 1
group by PeriodeFin, Commentaire, NumSalarie |
Partager