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
|
DECLARE @ActivitePlage AS TABLE(
DateActivite date,
Activite varchar(128),
Debut time(0),
Fin time(0)
)
INSERT INTO @ActivitePlage VALUES ('2015-05-04', 'Travail' , '08:00:00', '10:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-04', 'Travail' , '10:00:00', '12:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-04', 'Repas' , '12:00:00', '14:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-04', 'Conge' , '14:00:00', '17:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-05', 'Travail' , '08:00:00', '12:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-05', 'Repas' , '12:00:00', '14:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-05', 'Travail' , '14:00:00', '17:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-06', 'Travail' , '08:00:00', '09:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-06', 'RTT' , '09:00:00', '10:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-06', 'Travail' , '10:00:00', '12:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-06', 'Conge' , '12:00:00', '14:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-06', 'Conge' , '14:00:00', '17:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-07', 'Travail' , '08:00:00', '12:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-07', 'RTT' , '12:00:00', '14:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-07', 'RTT' , '14:00:00', '17:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-08', 'Conge' , '08:00:00', '12:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-08', 'Travail' , '12:00:00', '14:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-08', 'Conge' , '14:00:00', '17:00:00')
INSERT INTO @ActivitePlage VALUES ('2015-05-09', 'Repos' , NULL,NULL)
INSERT INTO @ActivitePlage VALUES ('2015-05-09', 'Repos' , NULL,NULL)
INSERT INTO @ActivitePlage VALUES ('2015-05-09', 'Repos' , NULL,NULL)
INSERT INTO @ActivitePlage VALUES ('2015-05-10', 'Repos' , NULL,NULL)
INSERT INTO @ActivitePlage VALUES ('2015-05-10', 'Repos' , NULL,NULL)
INSERT INTO @ActivitePlage VALUES ('2015-05-10', 'Repos' , NULL,NULL)
SELECT DISTINCT
DateActivite1,
-- Selection dans les plages regroupées de l'heure de debut au plus tot
(CASE
WHEN AAA.Fin1 between AAA.Debut2 AND AAA.Fin2 AND AAA.Debut2 < AAA.Debut1 THEN AAA.Debut2
ELSE AAA.Debut1
END) AS Debut,
-- Selection dans les plages regroupées de l'heure de fin au plus tard
(CASE
WHEN AAA.Fin1 < AAA.Fin2 AND AAA.Debut2 Between AAA.Debut1 AND Fin1 THEN AAA.Fin2
ELSE AAA.Fin1
END) AS Fin
FROM
(SELECT DISTINCT
AA1.DateActivite AS DateActivite1,
AA1.Debut AS Debut1,
AA1.Fin AS Fin1,
AA2.DateActivite AS DateActivite2,
AA2.Debut AS Debut2,
AA2.Fin AS Fin2
FROM
-- Toutes les plages d absences
(SELECT DateActivite, Debut, Fin FROM @ActivitePlage WHERE Activite = 'RTT' OR Activite='Conge') AA1
FULL JOIN
-- Ces plages sont jointes avec les plages pouvant etre regroupées
(SELECT DISTINCT A1.DateActivite, A1.Debut, A2.Fin
FROM
(SELECT DateActivite, Debut, Fin FROM @ActivitePlage WHERE Activite = 'RTT' OR Activite='Conge') AS A1
JOIN
(SELECT DateActivite, Debut, Fin FROM @ActivitePlage WHERE Activite = 'RTT' OR Activite='Conge') AS A2
ON A1.Fin = A2.Debut AND A1.DateActivite = A2.DateActivite) AA2
ON AA1.DateActivite = AA2.DateActivite) AAA |
Partager