Voici donc les réponses au challenge #2 pour lequel j’ai eu un peu plus de participants que la dernière fois et je vous en remercie tous, ces échanges étaient vraiment très sympathiques.
Un grand bravo à Nicolas et Antoine pour avoir proposé des bonnes solutions
Pour rappel, le sujet était de déterminer sur une plage de dates, le nombre d’heures de travail. Le sujet complet se trouve sur le premier post ici.
La difficulté ici était bien sur de pouvoir gérer une plage de dates entre celles de début et de fin. Tout autre mode calculatoire est à proscrire dans ce cas car cela engendrerai des calculs trop compliqués et non fiables.
Si la problématique n’avait pas été de faire tout cela en une requête cela aurait été relativement simple car il aurait suffit de créer une table temporaire que l’on aurait rempli par le biais d’une boucle pour chaque date de début et de fin. C’est toujours un exemple intéressant pour montrer comment faire des boucles toutes simples sans vilains curseurs:
DECLARE @D TABLE (ID INT,Date DATETIME)
DECLARE @ID INT, @FROM DATETIME, @TO DATETIME
SET @ID = 0
SELECT TOP 1 @ID = ID FROM @t WHERE ID > @ID
WHILE @@ROWCOUNT > 0
BEGIN
SELECT @FROM = StartDate, @TO = EndDate FROM @t WHERE ID = @ID
WHILE @FROM < @TO
BEGIN
INSERT INTO @D
SELECT @ID,@FROM
SELECT @FROM = DATEADD(DAY,1,@FROM)
END
INSERT INTO @D SELECT @ID,@TO
SELECT TOP 1 @ID = ID FROM @t WHERE ID > @ID
END
SELECT * FROM @D
Evidemment si ont part sur le principe qu’il faut tout faire en une seule requête la problématique est tout autre. Pour cela la meilleure solution reste d’utiliser les CTE qui même si elles sont plutôt faites pour créer des structures hiérarchiques peuvent très bien convenir pour de simples itérations.
Voici donc la solution que je vous propose:
DECLARE @t TABLE (ID INT IDENTITY, StartDate DATETIME, EndDate DATETIME)
SET DATEFORMAT MDY
SET DATEFIRST 1
INSERT INTO @t (StartDate, EndDate) SELECT '3/2/2009 8:00AM', '3/2/2009 3:00PM' --07:00
INSERT INTO @t (StartDate, EndDate) SELECT '3/1/2009 4:00PM', '3/3/2009 2:00 PM' --15:00
INSERT INTO @t (StartDate, EndDate) SELECT '2/26/2009 7:00AM', '2/26/2009 10:00PM' --09:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 AM', '1/27/2009 5:15 PM' --07:45
INSERT INTO @t (StartDate, EndDate) SELECT '1/17/2009 1:45 PM', '1/19/2009 7:45 AM' --00:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 9:15 PM', '1/28/2009 9:15 AM' --01:15
-- Add this other values to test weekends and invalid values
INSERT INTO @t (StartDate, EndDate) SELECT '3/7/2009 18:00PM', '3/17/2009 8:15AM' --54:15
INSERT INTO @t (StartDate, EndDate) SELECT '3/5/2009 18:00PM', '3/10/2009 7:00AM' --18:00
INSERT INTO @t (StartDate, EndDate) SELECT '1/27/2009 16:15 PM', '1/27/2009 9:15 AM' --0
-- Build the range of days between start and end with additional helper calcultations
;WITH BUILD_RANGES AS
(
-- select the exact start date based on business hours
SELECT T.ID
,StartDate =
CASE
WHEN DATEPART(HOUR,T.StartDate) < 8 THEN DATEADD(HOUR,8,DATEADD(DAY,0,DATEDIFF(DAY,0,T.StartDate)))
WHEN DATEPART(HOUR,T.StartDate) > 17 THEN DATEADD(HOUR,17,DATEADD(DAY,0,DATEDIFF(DAY,0,T.StartDate)))
ELSE T.StartDate
END
,T.EndDate
,BusinessDay = CASE WHEN DATEPART(DW,T.StartDate) IN (6,7) OR T.EndDate < T.StartDate THEN 0 ELSE 1 END
FROM @t T
UNION ALL
-- Select the other dates til the end
SELECT A.ID
,StartDate = DATEADD(HOUR,8,DateAdd(day,1,DATEADD(DAY,0,DATEDIFF(DAY,0,A.StartDate))))
,A.EndDate
,BusinessDay = CASE WHEN DATEPART(DW,DateAdd(day,1,A.StartDate)) IN (6,7) THEN 0 ELSE 1 END
FROM BUILD_RANGES A
INNER JOIN @t T ON A.ID = T.id
WHERE A.StartDate < T.EndDate
)
-- do final computation
SELECT ID,StartDate,EndDate,
HOURS = SUM(DATEDIFF(MINUTE,ComputedStart,ComputedEnd))/60,
MINUTES = SUM(DATEDIFF(MINUTE,ComputedStart,ComputedEnd))%60
FROM (
-- select final values with enddate based on business hours
SELECT O.Id
,ComputedStart = C.StartDate
,ComputedEnd =
CASE WHEN DATEADD(DAY,0,DATEDIFF(DAY,0,C.StartDate)) = DATEADD(DAY,0,DATEDIFF(DAY,0,O.EndDate))
THEN
CASE
WHEN DATEPART(HOUR,C.EndDate) < 8 THEN DATEADD(HOUR,8,DATEADD(DAY,0,DATEDIFF(DAY,0,C.EndDate)))
WHEN DATEPART(HOUR,C.EndDate) > 16 THEN DATEADD(HOUR,17,DATEADD(DAY,0,DATEDIFF(DAY,0,C.EndDate)))
ELSE CASE WHEN O.StartDate > O.EndDate THEN C.StartDate ELSE C.EndDate END
END
ELSE
DATEADD(HOUR,17,DATEADD(DAY,0,DATEDIFF(DAY,0,C.StartDate)))
END
,BusinessDay
,O.StartDate,O.EndDate
FROM BUILD_RANGES C
FULL JOIN @t O ON C.ID = O.ID
) FINAL_DATA
WHERE 1=1
AND (BusinessDay = 1 OR StartDate > EndDate)
AND DATEADD(DAY,0,DATEDIFF(DAY,0,ComputedStart)) <= DATEADD(DAY,0,DATEDIFF(DAY,0,EndDate))
GROUP BY ID,StartDate,EndDate
ORDER BY ID
GO
Les points d’intérêt:
La CTE
Nous faisons un union entre notre table de départ et le résultat de la requête lui-même de manière récursive en ajoutant 1 jour à chaque fois à la StartDate. On s’arrête avant le EndDate.
Récupérer le jour d’une date
cela n’a l’air de rien, mais vous ne pouvez pas simplement extraire d’une date (jour+heures) le jour sans les informations de temps! La solution la plus simple et la plus efficace est d’ajouter les jours de la date à la date 0:
DATEADD(DAY,0,DATEDIFF(DAY,0,C.StartDate))
Cette méthode est très pratique et efficace, vous pouvez la réutiliser pour tout autre type de calcul du même genre (calcul d’heure, calcul du premier jour de la semaine,etc…)
A bientôt pour un prochain challenge!
